Help using Base SAS procedures

ERROR: Sort execution failure in PROC SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

ERROR: Sort execution failure in PROC SQL

[ Edited ]

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.


Accepted Solutions
Solution
‎04-12-2016 12:17 PM
Trusted Advisor
Posts: 1,115

Re: ERROR: Sort execution failure.

[ Edited ]

Have you checked the hints in Usage Note 39705: Troubleshooting Sort Initialization and Sort Execution Failure errors in SQL proce...?

 

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.

View solution in original post


All Replies
Super User
Posts: 10,483

Re: ERROR: Sort execution failure.

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

 

group by DUPI, datepart(wagerdatetime);

Super Contributor
Posts: 312

Re: ERROR: Sort execution failure.

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.

Solution
‎04-12-2016 12:17 PM
Trusted Advisor
Posts: 1,115

Re: ERROR: Sort execution failure.

[ Edited ]

Have you checked the hints in Usage Note 39705: Troubleshooting Sort Initialization and Sort Execution Failure errors in SQL proce...?

 

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.

Super Contributor
Posts: 312

Re: ERROR: Sort execution failure.

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

I changed the work directory and it ran okay.

 

Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 8539 views
  • 1 like
  • 3 in conversation