topic Re: ERROR: Sort execution failure. in SAS Procedures

ERROR: Sort execution failure in PROC SQL

fengyuwuzu — Tue, 12 Apr 2016 16:30:56 GMT

I use SAS 9.4 under Linux, and use the following code :

libname Anawager "/HPCTMP_NOBKUP/home/data/analysis_wager";
libname test "/HPCTMP_NOBKUP/home/data/analysis_wager/test_04072016";

proc sql;
create table test.wager_per_day_all as
select DUPI, datepart(wagerdatetime) as date format =date9.,
sum(total_wager) as wager_per_day
from test.dupi_wager_b_all
group by DUPI, date;
quit;

but got the error:

3
4
5
6
7          proc sql;
8          create table test.wager_per_day_all as
9          select DUPI, datepart(wagerdatetime) as date format =date9.,
10         sum(total_wager) as wager_per_day
11         from test.dupi_wager_b_all
^L2                                                          The SAS System                                15:47 Friday, April 8, 2016

12         group by DUPI, date;
ERROR: Sort execution failure.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
13         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:28.87
      cpu time            1:32.04

14

ERROR: Errors printed on page 2.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
      real time           1:29.00
      cpu time            1:32.09

can anyone give some hints about this error? Thank you.

Re: ERROR: Sort execution failure.

ballardw — Fri, 08 Apr 2016 20:03:50 GMT

Since the variable DATE doesn't exist until writing the result I would try;

group by DUPI, datepart(wagerdatetime);

Re: ERROR: Sort execution failure.

fengyuwuzu — Fri, 08 Apr 2016 20:18:08 GMT

Thank you. I changed the date as datepart(wagerdatetime) but still got the same error.

previsouly I tested this code in Windows SAS 9.4 and it worked.

maybe some other reasons.

Re: ERROR: Sort execution failure.

FreelanceReinh — Tue, 12 Apr 2016 16:29:41 GMT

Have you checked the hints in Usage Note 39705: Troubleshooting Sort Initialization and Sort Execution Failure errors in SQL procedures in Windows and UNIX environments?

The note states:

Sort Initialization and Sort Execution errors might occur in an SQL procedure for several reasons. The errors are typically caused by a lack of disk space or memory or a less-than-optimal system option setting.

And the note offers several methods for checking available disk space and working around the constraints.

Re: ERROR: Sort execution failure.

fengyuwuzu — Tue, 12 Apr 2016 16:17:50 GMT

Thank you. Yes, the error is due to limited space for work directory.

I changed the work directory and it ran okay.

Thanks!