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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.