BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Aexor
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

6 REPLIES 6
Aexor
Lapis Lazuli | Level 10
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
Super User
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
Super User

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
Opal | Level 21

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
Ammonite | Level 13

@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

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 828 views
  • 6 likes
  • 5 in conversation