Sort Initialization and Sort Execution errors might occur in an SQL procedure for several reasons. The errors are typically caused by a lack of disk space or memory or a less-than-optimal system option setting.
Here are some troubleshooting tips to try when these errors occur.
Add the following statements before the code that produces the errors and review the SAS® log for the option settings:
proc options group=memory; run; proc options option=utilloc; run; proc options option=threads; run; options fullstimer msglevel=i ;
The FULLSTIMER option writes performance statistics to the SAS log.
If you are running SAS under UNIX, also add the following:
proc options option=workperms; run;
The MSGLEVEL=I option writes information to the SAS log about index processing and whether threaded sorts are being used. If threaded sorts are being used, this note is written to the SAS log:
NOTE: SAS threaded sort was used
Important: When the THREADS option is enabled, temporary utility directories and files created by the threaded sort are created in the directory specified by the UTILLOC option. These temporary utility directories and files are not created until they are needed by the sort routine.
In rare cases, the Sort Initialization Failure occurs because the submitting user ID does not have Read, Write, and Execute permissions for the WORK directory or the directory that is referenced by the UTILLOC option.
Under UNIX, verify that the SAS system option WORKPERMS is set appropriately. Also, ensure that the UNIX umask settings for the submitting user ID have Read, Write, and Execute authority for the directory that is referenced by the UTILLOC option.
A lack of space in the directories referenced by the WORK and UTILLOC options, to hold internal utility files created during sorts, can cause the Sort Execution errors.
To find the physical location for the SAS WORK library, submit this LIBNAME statement.
libname work list;
The physical location of the SAS WORK library is written to the SAS log. The location looks similar to the following. The last directory in each of the paths is the SAS WORK directory for the current SAS session.
UNIX Work Directory Location- Physical Name= /usr/tmp/SAS_work6A9A000451C0_xyz.sas.com
Windows Work Directory Location- Physical Name= C:\Users\userid\AppData\Local\Temp\SAS Temporary Files\_TD3708
Using the operating system's shell or explorer tool, navigate to the directory that holds all of the SAS WORK libraries. Using the sample paths shown above, change to one of the following directories, according to your operating system.
For UNIX systems: /usr/tmp
For Windows systems: C:\Users\userid\AppData\Local\Temp\SAS Temporary Files
Check the amount of free space that is available in the directory.
If space is limited in the directory that contains all of the SAS WORK libraries, look for directories that have names similar to the following and that are not currently in use by an active SAS session.
SAS_work6A9A000451C0_xyz.sas.com or _TD3708
Any such directories that you might find are most likely left over from abnormally terminated SAS sessions. Delete these folders to recover space. This must be done by an administrator of the system.
Monitor the space consumed as the sorting process is executing. This gives you an accurate view of the space used. Utility files are deleted after the sorting completes. Checking the amount of space available in the WORK or UTILLOC locations after the sort has completed can give the false impression that there is adequate space available.
If the FULLSTIMER memory statistic for the failing job shows that the amount of memory used is equal to or slightly greater than the MEMSIZE setting, you should increase the MEMSIZE value.
If you need further information before making changes to the MEMSIZE and SORTSIZE options, see the links at the bottom of this page for papers that contain detailed information about system tuning.
Which values you choose for MEMSIZE and SORTSIZE depends on the amount of physical RAM that is installed on your system. General suggested values for MEMSIZE are between 512M and 2G. The SORTSIZE value should be between 50% and 75% of MEMSIZE.
The MEMSIZE option is an invocation option and must be set in the sasv9.cfg configuration file, or specified when you start SAS.
> sas -memsize 1G -sortsize 512M
If the sort errors persist after the MEMSIZE and SORTSIZE values have been significantly increased, it is likely that the errors are due to a lack of disk space.
In some cases, depending on the value for SORTSIZE, decreasing the SORTSIZE value can prevent the error. A smaller SORTSIZE value can change the algorithm that the sort routine uses and cause it to choose sorting on disk instead of attempting an in-memory sort.
The information contained in this note might be applicable to other Base SAS procedures that perform sorts.
The following papers contain detailed information about system tuning:
|Product Family||Product||System||SAS Release|
|Macintosh on x64|
|Microsoft Windows 2000 Professional|
|Microsoft® Windows® for 64-Bit Itanium-based Systems|
|Microsoft Windows Server 2003 Datacenter 64-bit Edition|
|Microsoft Windows Server 2003 Enterprise 64-bit Edition|
|Microsoft Windows XP 64-bit Edition|
|Microsoft® Windows® for x64|
|Microsoft Windows 95/98|
|Microsoft Windows 2000 Advanced Server|
|Microsoft Windows 2000 Datacenter Server|
|Microsoft Windows 2000 Server|
|Microsoft Windows NT Workstation|
|Microsoft Windows Server 2003 Datacenter Edition|
|Microsoft Windows Server 2003 Enterprise Edition|
|Microsoft Windows Server 2003 Standard Edition|
|Microsoft Windows Server 2003 for x64|
|Microsoft Windows Server 2008|
|Microsoft Windows Server 2008 for x64|
|Microsoft Windows XP Professional|
|Windows 7 Enterprise 32 bit|
|Windows 7 Enterprise x64|
|Windows 7 Home Premium 32 bit|
|Windows 7 Home Premium x64|
|Windows 7 Professional 32 bit|
|Windows 7 Professional x64|
|Windows 7 Ultimate 32 bit|
|Windows 7 Ultimate x64|
|Windows Millennium Edition (Me)|
|Windows Vista for x64|
|64-bit Enabled AIX|
|64-bit Enabled HP-UX|
|64-bit Enabled Solaris|
|ABI+ for Intel Architecture|
|Linux for x64|
|Linux on Itanium|
|OpenVMS on HP Integrity|
|Solaris for x64|
|Date Modified:||2012-05-02 15:08:23|
|Date Created:||2010-05-20 09:58:35|