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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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