Architecting, installing and maintaining your SAS environment

SAS Proc Sql Sort Error

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 126
Accepted Solution

SAS Proc Sql Sort Error

Hi,

 

I am getting below error while submitting the code. Please advise?

 

ERROR: Sort execution failure.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
89 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 16:27.87
user cpu time 8:57.96
system cpu time 3:37.82
memory 12585386.64k
OS Memory 12604136.00k
Timestamp 04/12/2017 04:20:35 PM
Step Count 6 Switch Count 258
3 The SAS System 15:45 Wednesday, April 12, 2017

Page Faults 16
Page Reclaims 2978910
Page Swaps 0
Voluntary Context Switches 3831365
Involuntary Context Switches 2916139
Block Input Operations 2224
Block Output Operations 136374688

 

I have check below values in sas config file

-WORK /saswork
-MEMSIZE 48G
-SORTSIZE 12G
-WORKPERMS 700

 

There is no uttloc seperate entry in config file.


Accepted Solutions
Solution
‎04-12-2017 03:00 PM
Super User
Posts: 7,771

Re: SAS Proc Sql Sort Error

[ Edited ]
Posted in reply to japsas100

japsas100 wrote:

when execute below command its showing the lot of free disk space.

vmstat -s

65974172 total memory
13775512 used memory
10930572 active memory
1956100 inactive memory
52198660 free memory
226732 buffer memory
305372 swap cache
33554428 total swap
191288 used swap
33363140 free swap

 


These are all VIRTUAL MEMORY values. Nothing that deals with disk space (aside from the paging space) is found in vmstat.

Do a df -k on the work location.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Contributor
Posts: 49

Re: SAS Proc Sql Sort Error

Posted in reply to japsas100

Might be that there is not enough disk space - refer to this note: http://support.sas.com/kb/39/705.html

Super User
Posts: 7,771

Re: SAS Proc Sql Sort Error

Posted in reply to japsas100

90% (or 99%) you're out of space in WORK.

Try a separate proc sort by the variables needed.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 126

Re: SAS Proc Sql Sort Error

Posted in reply to KurtBremser

when execute below command its showing the lot of free disk space.

vmstat -s

65974172 total memory
13775512 used memory
10930572 active memory
1956100 inactive memory
52198660 free memory
226732 buffer memory
305372 swap cache
33554428 total swap
191288 used swap
33363140 free swap

 

Solution
‎04-12-2017 03:00 PM
Super User
Posts: 7,771

Re: SAS Proc Sql Sort Error

[ Edited ]
Posted in reply to japsas100

japsas100 wrote:

when execute below command its showing the lot of free disk space.

vmstat -s

65974172 total memory
13775512 used memory
10930572 active memory
1956100 inactive memory
52198660 free memory
226732 buffer memory
305372 swap cache
33554428 total swap
191288 used swap
33363140 free swap

 


These are all VIRTUAL MEMORY values. Nothing that deals with disk space (aside from the paging space) is found in vmstat.

Do a df -k on the work location.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 126

Re: SAS Proc Sql Sort Error

Posted in reply to KurtBremser

66gb of free space avaiable in saswork dir.

 

/dev/mapper/vg_sas-lv_saswork  99G 29G 66G 31% /saswork

 

Contributor
Posts: 49

Re: SAS Proc Sql Sort Error

Posted in reply to japsas100

How big is the file you are trying to sort? Could be that your MEMSIZE and SORTSIZE are to small

Super User
Posts: 7,771

Re: SAS Proc Sql Sort Error

Posted in reply to japsas100

Also have a look at the physical size(s) of the dataset(s) involved in the SQL, and how you wrote the SQL. Might well be that you caused a cartesian product that has you run out of space. Or you do a distinct that requires an unnecessarily complicated sort.

If in doubt, post your SQL code, and some metadata about the tables involved.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: SAS Proc Sql Sort Error

Posted in reply to KurtBremser

Did you review the http://support.sas.com/kb/39/705.html.
Decrease sortsize as suggested.

Check your code.

You can try with a smaller sample data

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 274 views
  • 2 likes
  • 4 in conversation