topic Re: SAS Proc Sql Sort Error in Administration and Deployment

SAS Proc Sql Sort Error

japsas100 — Wed, 12 Apr 2017 13:29:32 GMT

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.

Re: SAS Proc Sql Sort Error

Criptic — Wed, 12 Apr 2017 13:44:44 GMT

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

Re: SAS Proc Sql Sort Error

Kurt_Bremser — Wed, 12 Apr 2017 13:58:28 GMT

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

Try a separate proc sort by the variables needed.

Re: SAS Proc Sql Sort Error

japsas100 — Wed, 12 Apr 2017 14:00:47 GMT

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

Re: SAS Proc Sql Sort Error

Kurt_Bremser — Wed, 12 Apr 2017 14:03:36 GMT

@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.

Re: SAS Proc Sql Sort Error

japsas100 — Wed, 12 Apr 2017 14:05:58 GMT

66gb of free space avaiable in saswork dir.

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

Re: SAS Proc Sql Sort Error

Criptic — Wed, 12 Apr 2017 14:06:02 GMT

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

Re: SAS Proc Sql Sort Error

Kurt_Bremser — Wed, 12 Apr 2017 14:06:09 GMT

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.

Re: SAS Proc Sql Sort Error

thesasuser — Thu, 13 Apr 2017 17:46:51 GMT

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