BookmarkSubscribeRSS Feed
SASuserMY
Calcite | Level 5

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 NEDGLORODBCDATASRC=NEDGLORSCHEMA=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;

3 REPLIES 3
TarunKumar
Pyrite | Level 9

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

LIBNAME NEDGLORODBCDATASRC=NEDGLORSCHEMA=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;

jakarman
Barite | Level 11

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) 

---->-- ja karman --<-----
LinusH
Tourmaline | Level 20

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

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 846 views
  • 3 likes
  • 4 in conversation