SAS Proc Sql Sort Error

[japsas100]

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.

[Criptic]

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

[Kurt_Bremser]

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

Try a separate proc sort by the variables needed.

[japsas100]

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

[Kurt_Bremser · Accepted solution]

---

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

[japsas100]

66gb of free space avaiable in saswork dir.

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

[Criptic]

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

[Kurt_Bremser]

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.

[thesasuser]

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