topic Re: Choosing Three Earliest Dates in SAS Procedures

Choosing Three Earliest Dates

blackmetal — Wed, 24 Jun 2015 03:34:50 GMT

Hello SAS community,

I have the dataset below:

id term med start

1 1 w 20090110

1 1 x 20060324

1 1 y 20070704

1 1 z 20081124

1 2 w 20090110

1 2 x 20080324

1 2 y 20070704

1 2 z 20061124

2 ....

What I want to do is pick the earliest three start dates for each id-term pair.

id term med start

1 1 x 20060324

1 1 y 20070704

1 1 z 20081124

1 2 x 20080324

1 2 y 20070704

1 2 z 20061124

2 ....

I am not sure how to choose these dates using SAS Proc SQL and would appreciate your help. Thank you.

Re: Choosing Three Earliest Dates

gergely_batho — Wed, 24 Jun 2015 12:28:52 GMT

proc rank data=have out=want(where=(start_rank<=3));

var start;

ranks start_rank;

by id term;

run;

Re: Choosing Three Earliest Dates

Steelers_In_DC — Wed, 24 Jun 2015 12:37:18 GMT

Here's another solutions:

data have;

infile cards;

input id term med$ start;

cards;

1 1 w 20090110

1 1 x 20060324

1 1 y 20070704

1 1 z 20081124

1 2 w 20090110

1 2 x 20080324

1 2 y 20070704

1 2 z 20061124

;

data all want(drop=count);

set have;

by id term;

count + 1;

if first.id then count = 1;

if first.term then count = 1;

output all;

if count < 4 then output want;

run;

Re: Choosing Three Earliest Dates

MadhuKorni — Wed, 24 Jun 2015 13:24:40 GMT

proc sort data = have;

by id term start;

data want;

set have;

retain cnt ;

if first.id or first.term then cnt = 1;

else cnt+1;

by id term;

if cnt le 3;

drop cnt;

run;