Hi! I need your help to output the Top 2 scores, including ties, from this dataset.
/*Have*/
data have;
input name $ gender $ score;
cards;
Ann F 100
May F 100
Jean F 90
Leah F 89
Felix M 100
Chris M 95
Greg M 90
;
run;
/*Output Top 2 for Each Gender, Including Ties*/
data want;
input name $ gender $ score;
cards;
Ann F 100
May F 100
Jean F 90
Felix M 100
Chris M 95
;
run;
Thank you!
If your data is already sorted by gender and you don't mind creating a new variable :
proc rank data=have out=want(where=(_order<=2)) ties=dense descending;
by gender;
var score;
ranks _order;
run;
proc sort data= have ;
by gender descending score;
run;
data want (where=(rank le 2) drop= lag_score);
set have;
retain rank;
lag_score=lag(score);
by gender descending score;
if first.gender then rank = 1 ;
else do;
if score=lag_score then rank + 0;
else rank +1 ;
end;
run;
If your data is already sorted by gender and you don't mind creating a new variable :
proc rank data=have out=want(where=(_order<=2)) ties=dense descending;
by gender;
var score;
ranks _order;
run;
Or, if like me, you prefer to avoid the lag functions:
proc sort data=have ;
by gender descending score;
run;
data want;
do until(last.gender);
set have; by gender descending score;
order = sum(order, first.score);
if order <= 2 then output;
end;
drop order;
run;
You are right, the lag is not needed. out of interest why do you avoid lag functions - performance reasons or something else?
Because they are not true lag functions but rather ill-designed FIFO queue functions. I mostly feel unsafe programming with those.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.