topic Re: Help with efficiency dealing with large dataset (find first/last visit date) in SAS Programming

Help with efficiency dealing with large dataset (find first/last visit date)

browste — Wed, 27 Mar 2013 15:48:39 GMT

Hello,

I am working with a dataset that has a few million records, and instead of sorting by id and visit date, then using a data step to take the first. and last. visit dates for each id I would be interested in a more efficient way to get the data. The proc sort on the dataset takes forever. Any help would be appreciated. Thanks!

-Steve

Re: Help with efficiency dealing with large dataset (find first/last visit date)

ballardw — Wed, 27 Mar 2013 16:15:34 GMT

I would be tempted to use PROC SUMMARY and CLASS statements.

Something like:

proc summary data =have ;

class ID;

var VisitDate;

output out=want max min /autoname;

run;

Proc sql;

create table want as

select id, min(visitdate) as firstdate, max(visitdate) as lastdate

from have

group by id;

quit;

Re: Help with efficiency dealing with large dataset (find first/last visit date)

Haikuo — Wed, 27 Mar 2013 16:20:49 GMT

If your source data is something other than SAS table, say Oracle, SQL server, then you will have an option doing it using pass-thru. Other than that, you are stuck with Proc Sort. I doubt if Hash table could help, but first you need to make sure your whole table can be fitted into your RAM, and even if it can, I suspect that the Hash sorting would be more efficient than Proc sort.

my 2cents,

Haikuo

Re: Help with efficiency dealing with large dataset (find first/last visit date)

browste — Wed, 27 Mar 2013 16:31:14 GMT

Thanks so much ballardw, both of those are much, much quicker. I really appreciate it!

-Steve