DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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


Accepted Solutions
Solution
‎03-27-2013 12:15 PM
Super User
Posts: 10,500

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

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;

or

Proc sql;

     create table want as

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

     from have

     group by id;

quit;

View solution in original post


All Replies
Solution
‎03-27-2013 12:15 PM
Super User
Posts: 10,500

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

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;

or

Proc sql;

     create table want as

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

     from have

     group by id;

quit;

Respected Advisor
Posts: 3,124

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

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

New Contributor
Posts: 2

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

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

-Steve

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 177 views
  • 0 likes
  • 3 in conversation