topic Re: Handling big datasets in SAS Programming

Handling big datasets

anandbillava — Wed, 28 Apr 2010 14:39:18 GMT

I am creating a report using proc report which pulls data from oracle databse using proc sql. It takes lot of time when i run it in my local SAS and it crashes when running in Enterprise guide. It gives error saying ERROR: Utility file write failed. Probable disk full condition.
or dataset is corrupted. Some of the datasets in this joining has more than 200,000 records.

Once the above dataset created from proc sql there are some internal processing required. Some time it crashes during those steps saying dataset is corrupted.

Any idea about handling such a big table ? Any tips would be appreciated..
Thanks in advance.

Message was edited by: anandbillava

Message was edited by: anandbillava

Message was edited by: anandbillava Message was edited by: anandbillava

Re: Handling big datasets

Doc_Duke — Wed, 28 Apr 2010 16:02:37 GMT

You just need more disk space for UTILLOC. Search for on support.sas.com

Re: Handling big datasets

LinusH — Mon, 03 May 2010 11:58:07 GMT

So you are running EG using a local SAS installation, with ACCESS to Oracle?
If so, try to make the join to happen in Oracle. Use either explicit SQL pass-thru, or try to make your SQL join as clean as possible to make SAS to do a implicit pass-thru.
This will probably make the disk full problem go away.

/Linus

Re: Handling big datasets

Peter_C — Wed, 05 May 2010 12:38:52 GMT

anandbilliva

to see what is going on under the syntax layer, use the system option SASTRACE, like:
options sastrace=',,,d' sastraceloc=FILE "~somewhere/problem1.log" nostsuffix;
Additionally, in the proc sql statement, add option _TREE.
When you get a lot of details in the SASlog from _TREE, probably your query will be inefficient. You want a report from _TREE showing that the bulk of the query has been passed to the database server.

good luck
PeterC