topic Issue in sorting a large dataset in SAS Procedures

Issue in sorting a large dataset

archana — Wed, 07 May 2014 14:55:50 GMT

HI,

I am trying to sort a large dataset with 367538640 rows . Sort is getting failed because of the space issue. Tried using options compress =yes and tagsort . Tagsort is taking very long time. Please suggest any alternatives.

Thanks,

Re: Issue in sorting a large dataset

JacobSimonsen — Wed, 07 May 2014 16:54:48 GMT

It is likely that the problem is that SAS creates a copy of the dataset, which will overwrite the original data when the sort procedure is finish. Therefore, if you have a system where there is more space on the drive where permanent data is supposed to be saved, then your can tell sas that it should use that drive as a work-directory. But, remember to change back afterwards.

If you use windows you should add

-work "d:\path_to_temporary_workfolder"

in the command line from where you start sas.

see the documentation here: http://support.sas.com/documentation/cdl/en/lesysoptsref/66899/HTML/default/viewer.htm#p1er6tm8fay8u2n1fhktmeoy2be4.htm

Re: Issue in sorting a large dataset

jakarman — Wed, 07 May 2014 20:12:56 GMT

Tagsort and compressing the definitive dataset will not help you much.

The sorting requires apx 3 times the sizing of the original dataset as intermediate work.

Overwriting the original datasets is adding the need of one additional copy.You can redirect the intermediate work to an other location using utilloc system option.

I am assuming you are using a server of some kind with a limited setup in this 365M records is a big number what is the size of that? if a recordsize is 100 bytes it should by 36Gb.

Unless your logical requirement is absolutely needing the sort there are possible better solutions to your original question.

Needing this sort really, you could try to split this big data set in multiple smaller ones and merge the several sorted smaller ones in a dedicated step.

Re: Issue in sorting a large dataset

Kurt_Bremser — Thu, 08 May 2014 05:33:10 GMT

UTILLOC in the configuration file allows you to specify a location different from WORK for the temporary sort file. This will reduce the requirement for the file to be sorted to 2x.

If you do

proc sort data=x1.xxx out=x2.xxx;

where x1 and x2 are libraries on different file systems, this may also help preventing an out of space condition, because you "only" need the size of xxx to be free one time in the UTILLOC and the x2 location, alike.

Then I recommend what Jaap suggested, split the file, sort every partial file on its own, and then do:

data want;

set

have1

have2

...

haven

;

by sortcrit;

run;

This is called interleaving, the sort order is preserved.