Need help to sort data in best possible way

[Aexor]

Hi All,

I am getting error in a particular proc sql step where "örder by " "is used, but I am getting sort execution error where order by is used

I got few other alternatives to sort but all of these taking same execution time

Three Alternatives To PROC SORT In SAS - SASnrd

Can anyone tell which one is best to use of "ERROR: Sort execution failure" error .

Thank you!!

[Kurt_Bremser]

Don't do too much in a single PROC SQL step; if removing the ORDER BY clause fixes your ERROR, add a follow-up PROC SORT step to get the required order.

[Aexor]

I have added a PROC SORT previous to the step, but I am getting
ERROR: An I/O error has occurred on file WORK.<file name>

[Reeza]

I think you need to post your actual code and log please.

Any chance you're running out of space? If you're sorting a large file, you usually need 3x that file size available for sort.

[Kurt_Bremser · Accepted solution]

So the final sort seems to overflow your WORK, which also explains the failure of your SQL.

• Try the TAGSORT option in PROC SORT (especially if you have the COMPRESS system option set)
• Clean up your WORK as much as possible before running the sort

Also post your code and log, so we can see what really happens. Post the complete log(all code and messages).

[Patrick]

This I/O error message normally occurs if you run out of available disk space. 

Both Proc Sort and a SQL Order By use UTILLOC - which often is the same location as WORK. 

WORK and UTILLOC are both locations on disk under paths created by SAS for the duration of a SAS Session. 

You can free-up disk space by deleting tables in WORK that you don't need anymore.

If you're running things on a local SAS installation on your laptop then you might just need to clean-up so you've got more free disk space.

If you're running SAS on a server then normally there is sufficient disk space available for WORK unless it's an undersized environment. If running on a server then I'd also verify that your SQL is doing what it should and that you are not having some many:many Cartesian join that creates a huge data volume. 

[AhmedAl_Attar]

@Aexor 

It would help more if you could provide additional context.

- Where you data originated from

• Database ----> Use Proc SQL Explicit Pass-through  to make the Database perform the sort for you.
• Flat File(s) ----> Some Operating system have built-in sort commands, you can use those command to sort the data before reading into SAS.
• SAS data set ----> SAS Settings (-sortsize xG -memsize yG -cpucount z -utilloc /? ) and File-system  I/O throughput will play major role in performance

- Size of your data file in GB rather than record count

- Compressed/Uncompressed status

Here is a paper that might of interest to Solving SAS® Performance Problems: Our Methodology 

Hope this helps