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.

Answer 1

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.