BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

5 REPLIES 5
34reqrwe
Quartz | Level 8
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;
PGStats
Opal | Level 21

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;
PG
PGStats
Opal | Level 21

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;
PG
34reqrwe
Quartz | Level 8

You are right, the lag is not needed. out of interest why do you avoid lag functions - performance reasons or something else?

PGStats
Opal | Level 21

Because they are not true lag functions but rather ill-designed FIFO queue functions. I mostly feel unsafe programming with those.

PG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2215 views
  • 3 likes
  • 3 in conversation