topic Re: Need help to sort data in best possible way in SAS Programming

Need help to sort data in best possible way

Aexor — Thu, 25 May 2023 17:18:14 GMT

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!!

Re: Need help to sort data in best possible way

Kurt_Bremser — Thu, 25 May 2023 17:24:06 GMT

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.

Re: Need help to sort data in best possible way

AhmedAl_Attar — Thu, 25 May 2023 18:39:45 GMT

@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

Re: Need help to sort data in best possible way

Aexor — Fri, 26 May 2023 15:18:28 GMT

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>

Re: Need help to sort data in best possible way

Reeza — Fri, 26 May 2023 15:26:18 GMT

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.

Re: Need help to sort data in best possible way

Kurt_Bremser — Fri, 26 May 2023 17:38:42 GMT

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).

Re: Need help to sort data in best possible way

Patrick — Sat, 27 May 2023 01:46:59 GMT

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.