topic Re: Sorting large datasets on multiple variables in SAS Programming

Sorting large datasets on multiple variables

div44 — Fri, 28 Oct 2016 00:05:32 GMT

Hello everyone,

I have been trying to sort a large dataset on 4 variables. The dataset is 100GB and it is using up the entire work space in the background and is unable to complete the process. Is there any efficient way other than the traditional proc sort or proc sql. I have tried both.

PROC SORT DATA = xyz;

BY a b c d;

RUN;

PROC SQL;

CREATE TABLE xyz1

SELECT *

FROM XYZ

ORDER BY a,b,c,d;

QUIT;

Thank you for your time

Re: Sorting large datasets on multiple variables

SASKiwi — Fri, 28 Oct 2016 00:26:35 GMT

Try compressing your table first then sorting with compression on:

options compress = binary;

DATA xyz;
  set xyz;
run;

proc sort data = xyz;
etc....

Re: Sorting large datasets on multiple variables

div44 — Fri, 28 Oct 2016 00:57:22 GMT

Hello,

Thanks for the suggestion. The dataset which I am using is compressed
already.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Sorting large datasets on multiple variables

Reeza — Fri, 28 Oct 2016 01:10:15 GMT

100GB on anything is going to be a bit of a nightmare.

What are are you trying to achieve?

Re: Sorting large datasets on multiple variables

SASKiwi — Fri, 28 Oct 2016 05:48:38 GMT

Ok, then sort the dataset in chunks and interleave the chunks into one final sorted version:

proc sort data = xyz (firstobs = 1 obs = 100000)
               out = chunk1
              ;
  by a b c d;
run;

proc sort data = xyz (firstobs = 100001 obs = 200000)
               out = chunk2
              ;
  by a b c d;
run;

data final
  set chunk1
      chunk2;
  by a b c d;
run;

Re: Sorting large datasets on multiple variables

Reeza — Fri, 28 Oct 2016 02:22:10 GMT

@SASKiwi That doesn't guarantee that the chunks will be in the overall correct sort order, does it?

Does it find the 'smallest' records required and then put them to the output data set?

Re: Sorting large datasets on multiple variables

PGStats — Fri, 28 Oct 2016 02:37:58 GMT

Have you considered using the TAGSORT option?

Re: Sorting large datasets on multiple variables

Ksharp — Fri, 28 Oct 2016 03:06:45 GMT

One way is spliting this big table into many small tables and combine them later.

data F_11 M_11 ......;
 set big;
 select;
 when(sex='F' and age=11) output F_11;
 ..........



data want;
 set F_11 M_11............

Re: Sorting large datasets on multiple variables

Astounding — Fri, 28 Oct 2016 03:15:21 GMT

If you take the approach of splitting the data set up and sorting the pieces, you can improve the speed a little if you know something about the distribution of the first BY variable. For example:

data a1 a2 a3;

set huge;

if a < '4' then output a1;

else if a < '6' then output a2;

else output a3;

run;

After sorting A1, A2, and A3 (BY A B C D), you can combine the sorted pieces more simply:

data want;

set a1 a2 a3;

run;

A simple SET statement will be faster than anything that involves a BY statement for this final step.

Re: Sorting large datasets on multiple variables

div44 — Fri, 28 Oct 2016 03:45:34 GMT

I have not used it. Does it reduce the background memory usage ?

Re: Sorting large datasets on multiple variables

PGStats — Fri, 28 Oct 2016 04:20:12 GMT

That's what the doc says

"The TAGSORT option is useful in single-threaded situations where there might not be enough disk space to sort a large SAS data set. The TAGSORT option is not supported for multi-threaded sorts.

When you specify the TAGSORT option, only sort keys (that is, the variables specified in the BY statement) and the observation number for each observation are stored in the temporary files. The sort keys, together with the observation number, are referred to as tags. At the completion of the sorting process, the tags are used to retrieve the records from the input data set in sorted order. Thus, in cases where the total number of bytes of the sort keys is small compared with the length of the record, temporary disk use is reduced considerably. However, you should have enough disk space to hold another copy of the data (the output data set) or two copies of the tags, whichever is greater. Note that although using the TAGSORT option can reduce temporary disk use, the processing time might be much higher."

Re: Sorting large datasets on multiple variables

SASKiwi — Fri, 28 Oct 2016 05:52:07 GMT

@Reeza I would have thought that SET plus BY will interleave the chunks to maintain the correct sorted order. Happy to be put right if that is not correct.

Re: Sorting large datasets on multiple variables

Kurt_Bremser — Fri, 28 Oct 2016 06:30:25 GMT

@Reeza wrote:

@SASKiwi That doesn't guarantee that the chunks will be in the overall correct sort order, does it?

Does it find the 'smallest' records required and then put them to the output data set?

It has been my experience (up to now) that using equally sorted datasets in one set statement, followed by the respective by, will keep the sort order in the output.

%macro test_sort (num);
%do i = 1 %to &num;
proc sort data=sashelp.class out=class&i;
by weight height age sex name;
run;
%end;

data want;
set
%do i = 1 %to &num;
  class&i
%end;
;
by weight height age sex name;
run;
%mend;
%test_sort(5);
proc print data=want;run;

Re: Sorting large datasets on multiple variables

LinusH — Fri, 28 Oct 2016 06:36:50 GMT

Assuming that you already set the maximum values for MEMSIZE and SORTSIZE.
Disk is cheap, expanding your work drive is a no brainer with data sizes like that.

Re: Sorting large datasets on multiple variables

Kurt_Bremser — Fri, 28 Oct 2016 06:54:38 GMT

@SASKiwi wrote:

Try compressing your table first then sorting with compression on:
options compress = binary;

DATA xyz;
  set xyz;
run;

proc sort data = xyz;
etc....

I just ran a test here with SAS 9.2 on AIX.

The compress system option does not affect the utility file that is created during the sort, so sorting a compressed dataset of 100 GB might easily eat 1 TB of WORK (although the final output will once again be only 100 GB).

Sorting large compressed datasets is done best with the tagsort option.

Re: Sorting large datasets on multiple variables

SuryaKiran — Fri, 28 Oct 2016 20:29:17 GMT

@Kurt_Bremser The macro code you provided creates 5x19 observations. Its not subsetting the data. It is just repeating 5 times.

Re: Sorting large datasets on multiple variables

SuryaKiran — Fri, 28 Oct 2016 20:31:39 GMT

I preffer to break the dataset into smaller ones and sort. Check the code below:

%macro split (dsn=, sets=);

%local first /* first observation */

last /* last observation */

n /* number of obs */

subset /* numbers subsets */

perblock /* obs per subset */

;

%let first=1;

%let subset=1;

data _null_;

if 0 then set &DSN nobs=nobs;

call symput('N', put(nobs, 9.));

call symput('perblock',put(ceil(nobs/&SETS), 9.));

stop;

run;

%if &N > 5 and &SETS > 1 %then /* N > your-choice */

%do %until (&LAST >= &N);

%let last = %eval(&FIRST +&PERBLOCK - 1);

proc sort data=&DSN

(firstobs=&FIRST obs=&LAST)

out=subset&SUBSET;

by name;

run;

%let first = %eval(&last + 1);

%let subset = %eval(&subset + 1);

%end;

%else %do;

proc sort data=&dsn;

by name;

run;

%end;

data one;

set

%do i = 1 %to &sets;

subset&i

%end;

;

by name;

run;

%mend split;

%split (dsn=sashelp.class, sets=4);

Re: Sorting large datasets on multiple variables

rogerjdeangelis — Fri, 28 Oct 2016 23:50:08 GMT

I sort datasets like this all the time on my inexpensive <$1000 of lease dell T7400 with 64gb and two raid 0 SSD arrays on separate channels. For moderate size data like yours I would partition the raw input at the time you created it using SPDE(free with base SAS). The T7400's have 8 cores so you could run say 7 parallel jobs. Also split the sort utility files so are not in respective 7 work directories.

SAS workstation supports 16 cores.

If you are sorting on a large numeric key you can mod the key and run 7 parallel jobs. I like to use a view to set the 7 pieces back together, no by statement needed. Read time should be the same as reading a single physical file? Actually it is often nice to have the pieces for future parallelism.

There are other techniques if you have an index or a low cardinality uniform grouping variable(not skewed grouping variable.

You can use firstobs and obs to read 7 partitions but you will neee a by statement fo the final view.

I switch to a server when a single table is greater than 1TB, I call this big bata.

Re: Sorting large datasets on multiple variables

Patrick — Sat, 29 Oct 2016 00:37:59 GMT

I consider it as a sub-optimal approach if you have to partially hand code sorting only because of a lack of disk space. May be have a chat with your SAS Admin if there is something that could be done.

You want a fast disk for UTILLOC but it doesn't have to be the same than WORK and it can also be pointed to multiple disks.

If extending disk space is not an option then I'd go for TAGSORT as already suggested. Creation of a sorted data set will likely take much longer but you shouldn't run out of disk space anymore.

Re: Sorting large datasets on multiple variables

Kurt_Bremser — Sat, 29 Oct 2016 11:06:14 GMT

@SuryaKiran wrote:

@Kurt_Bremser The macro code you provided creates 5x19 observations. Its not subsetting the data. It is just repeating 5 times.

You completely missed what I was pointing at. I just wanted to illustrate that using a set and by statement to combine separately sorted datasets will preserve the correct sort order in the output dataset.

Please observe that this discussion thread deals with sorting, not subsetting.