topic Proc Sort causing my temporary file burst my storage in SAS Programming

Proc Sort causing my temporary file burst my storage

SASuserMY — Wed, 07 Jan 2015 06:36:42 GMT

Hi, as per my question above. I'm using SQL dataware house and doing query through ODBC.

When it run until the second part of the code which is sorting the dataset, the temp file getting bigger and bigger until burst my storage.

Anyone know what is the issue? Thank you.

My code as below.

Where the create date and complete date is in Datetime22.3 format and the number of observation around 13 million and my available storage around 70GB.

LIBNAME NEDGLOR

ODBC

DATASRC=NEDGLOR

SCHEMA=DBO;

PROC SQL;

CREATE TABLE SASUSER.TBL_WF_ST_ACT_USER AS SELECT

	PRO_ID,
	CREATE_DATE,
	ACT_DEF_NAME,
	APP_STATUS_CODE,
	COMPLETE_DATE,
	CUSTOM_COLUMN1

FROM NEDGLOR.TBL_WF_ST_ACT_USER

;

QUIT;

PROC SORT DATA=TBL_WF_ST_ACT_USER; BY PRO_ID DESCENDING CREATE DATE DESCENDING ACT_DEF_NAME; RUN;

Re: Proc Sort causing my temporary file burst my storage

TarunKumar — Wed, 07 Jan 2015 07:55:18 GMT

Not sure but try this ==> OPTION COMPRESS = YES RESUE=YES;

LIBNAME NEDGLOR

ODBC

DATASRC=NEDGLOR

SCHEMA=DBO;

OPTION COMPRESS = YES RESUE=YES;

PROC SQL;

CREATE TABLE SASUSER.TBL_WF_ST_ACT_USER AS SELECT

	PRO_ID,
	CREATE_DATE,
	ACT_DEF_NAME,
	APP_STATUS_CODE,
	COMPLETE_DATE,
	CUSTOM_COLUMN1

FROM NEDGLOR.TBL_WF_ST_ACT_USER

;

QUIT;

PROC SORT DATA=TBL_WF_ST_ACT_USER; BY PRO_ID DESCENDING CREATE DATE DESCENDING ACT_DEF_NAME; RUN;

Re: Proc Sort causing my temporary file burst my storage

jakarman — Wed, 07 Jan 2015 09:06:01 GMT

When you have an database-query being executed than DO NOT sort in SAS but retrieve the dataset ordered (SQL ordered by clause).

When it is possible have the RDBMS execute as much as possible and do not copy the data back to SAS for ease of coding too quickly.

Is it required to copy data to the SAS environment have it compressed in the best possible way (compress=binary) for saving size and also IO time with spinning disks.

The Sort/utility files are not affected by compressing and will not help with that.

Even the bufsize en bufno were not tunable with utility-files before 9.4 SAS(R) 9.4 System Options: Reference, Third Edition (ubufsize ubufno)

Re: Proc Sort causing my temporary file burst my storage

LinusH — Wed, 07 Jan 2015 09:29:16 GMT

This is totally a SAS thing, so your SQL Server/ODBC connection takes no part.

For a normal sort, you need to have 3 times the size of the source table available + the size for the target table.

A few things that you could test:

Use Order By in your SQL. Chances are that the sort will be pushed down to your SQL Server.
Expand your WORK directory
Expand your MEMSIZE ans SORTSIZE system options.
Use TAGSORT in PROC SORT. Takes quite longer time, but uses less memory.

I guess this is just a small code snippet, but consider not to move your data to SAS, it's just a sort...?