topic Re: Sorting large data in SAS Programming

Sorting large data

Emma2021 — Fri, 07 Jan 2022 03:44:35 GMT

Data are not sorted. I am running below codes:
Proc sort data=tmp; by id name var;

Data tmp1;
Set tmp;
By id name var;
If first.name and last.var;
Run;

How can I do this without sorting?
My data is a very large and it is taking over an hour to just sort. Any help?
Thank you

Re: Sorting large data

SASKiwi — Fri, 07 Jan 2022 03:42:19 GMT

Where is the dataset tmp coming from? If it is already in the right data order then you don't need to sort again.

Have you tried your program without the sort and if so what does your SAS log report?

Re: Sorting large data

Emma2021 — Fri, 07 Jan 2022 03:43:53 GMT

They are not sorted by those 3 key variables.

Re: Sorting large data

andreas_lds — Fri, 07 Jan 2022 06:18:07 GMT

We need more information, so please post proc contents of your dataset "tmp". This will show the size of the dataset along with information about the variables contained, maybe tagsort could be used. Also please post some lines of the dataset and the expected result - as always as a working data step, maybe another approach is possible.

Re: Sorting large data

Kurt_Bremser — Fri, 07 Jan 2022 08:29:47 GMT

Please post the complete output of PROC CONTENTS for your dataset.

In which environment do you run this code (single machine or client-server)?

Re: Sorting large data

ballardw — Fri, 07 Jan 2022 15:50:17 GMT

Have you tried the TAGSORT option? This reduces the size of temporary utility files used during sort and if part of the time is related to moving lots of data in/out of the utility set may reduce execution time. May not.

Proc sort data=tmp tagsort; 
   by id name var;
run;

@Emma2021 wrote:
Data are not sorted. I am running below codes:
Proc sort data=tmp; by id name var;

Data tmp1;
Set tmp;
By id name var;
If first.name and last.var;
Run;

How can I do this without sorting?
My data is a very large and it is taking over an hour to just sort. Any help?
Thank you

Re: Sorting large data

Emma2021 — Fri, 07 Jan 2022 15:51:26 GMT

There are only 4 variables —all about 200 length string except var is numeric integer data.

Re: Sorting large data

Kurt_Bremser — Fri, 07 Jan 2022 16:12:48 GMT

I asked for the complete output of PROC CONTENTS for a purpose, not because I am extremely bored.

Re: Sorting large data

LinusH — Mon, 10 Jan 2022 15:37:31 GMT

First, make sure you have your memory options set to utilize as much as possible of your working memory (MEMSIZE, SORTSIZE).

But as some point you need to sort that data.

You could ommit storing a tempory data set (probably not the the biggest part of the processing) by using a single SQL statement:

proc sql;
create table tmp1 as
select id, name, var
from tmp
group by id, name, var
having count(*) = 1;
quit;

Re: Sorting large data

SASKiwi — Mon, 10 Jan 2022 19:35:44 GMT

Long character variables like 200 characters take up a lot of space resulting in large datasets. Using the COMPRESS = YES SAS option might reduce your dataset sizes and speed up your processing.