topic Re: Rank Observations and Output Ties in SAS Programming

Rank Observations and Output Ties

angeliquec — Thu, 04 Apr 2019 01:50:21 GMT

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!

Re: Rank Observations and Output Ties

34reqrwe — Thu, 04 Apr 2019 04:02:30 GMT

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;

Re: Rank Observations and Output Ties

PGStats — Thu, 04 Apr 2019 04:04:31 GMT

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;

Re: Rank Observations and Output Ties

PGStats — Thu, 04 Apr 2019 04:14:03 GMT

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;

Re: Rank Observations and Output Ties

34reqrwe — Thu, 04 Apr 2019 04:31:44 GMT

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

Re: Rank Observations and Output Ties

PGStats — Thu, 04 Apr 2019 04:52:25 GMT

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