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 ....
2
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 ....
2
2
I am not sure how to choose these dates using SAS Proc SQL and would appreciate your help. Thank you.
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;
proc rank data=have out=want(where=(start_rank<=3));
var start;
ranks start_rank;
by id term;
run;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.