Sort execution failure in SAS

Question

I encountered an error message when I was running PROC SQL function in SAS.

Below is my command:

proc sql;
     create table abc1
     as select a.endfyr,a.begfyr,a.datadate, b.*
     from data.table1 as a
     right join
     data.CRSP_19792013 as b
     on a.lpermno=b.permno and a.begfyr<=b.date<=a.endfyr;
quit;

Below is the results from log file: GOPTIONS ACCESSIBLE;

proc sql;
     create table abc1
     as select a.endfyr,a.begfyr,a.datadate, b.*
     from data.table1 as a
     right join
     data.CRSP_19792013 as b
     on a.lpermno=b.permno and a.begfyr<=b.date<=a.endfyr;
quit;

- NOTE: SAS threaded sort was used.

- ERROR: Sort execution failure.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 35.54 seconds
user cpu time 16.03 seconds
system cpu time 15.08 seconds
memory 271858.12k
OS Memory 277812.00k
Timestamp 09/25/2014 04:02:32 AM

After searching online for solutions, I found that the error Sort execution failure could be due to lack of memory. I did increase memsize and sortsize. But they are not helpful. However, the memory and OS memory stated above (log file results) is suggesting something? data.CRSP_19792013 is indeed a large file.

Should you require further info to help me, please do let me know.

You don't specify the values of SORTSIZE and MEMSIZE nor the size of the table. To sort a table, you need ~ 3x the space (either in memory or on disk); do you have that? Also, do you have an index on your tables? — Joe
You could split your large dataset into a number of smaller ones, sort each one, then interleave (set with by statement) them back together. — Chris J
A couple weeks ago one of our SAS BI servers was having problems. I got the same error with any SQL step that had an order by clause, regardless of size. They bounced the servers and it fixed the problem. Might want to check if you get the same error message from small tables. — Quentin
As a first attempt: sort table1 on lpermno and CRSP_19792013 on permno before joining. If that does not help, go for a data step. A merge will not work because of the inequalities, so you need set and retain. Attention if you have a n-m match, though — Dirk Horsten

Unknown Unknown · Accepted Answer · 2018-08-30T12:17:32

If you are working on z/OS, use "prco sql nothreads;"

If the SAS threaded sort is used, the host sort is not used. But the host sort is much better.

Sort execution failure in SAS

1 Answers