DATA Step, Macro, Functions and more

Ranking

Reply
Super Contributor
Posts: 673

Ranking

ttype has two values 'MTH' and 'YTD'.if we want to display ranks of districts on these two values (grouping) such that we see how districts are ranked in a month and how they are ranked in year?


The below code gives ranks from 1 to 66 .instead we need rank 1 to 33 in mth and 1 to 33 in year to be shown in a single dataset
proc sql;

create table rank_dist_before as

select product, metric,lbl,ttype,dist,sum(sls) as sls

from tmp1

group by dist,ttype

order by sls descending;

quit;

data rank_dist_nation;
set rank_dist_before;
Rank+1;
run;
SAS Super FREQ
Posts: 8,866

Re: Ranking

Hi:
If you are assigning the RANK variable in a program, this would be a good point to investigate BY group processing and the use of FIRST.byvar logic (also LAST.byvar).

cynthia

ps...there is also a PROC RANK procedure that you might be able to use here -- I don't use it much, but perhaps someone else can help you with it.
Super Contributor
Posts: 673

Re: Ranking

Posted in reply to Cynthia_sas
I tried Proc rank,but it gives me the same rank for all rows.
Trusted Advisor
Posts: 2,115

Re: Ranking

modify the DATA step to include a BY statement and explicitly reset the retain'd variable.

data rank_dist_nation;
set rank_dist_before;
BY sls;
IF first.sls THEN rank=0;
RETAIN rank;
Rank+1;
run;
Super Contributor
Posts: 673

Re: Ranking

1 is assigned to all rows.
N/A
Posts: 0

Re: Ranking

try this...

proc sql;

create table rank_dist_before as

select product, metric,lbl,ttype,dist,sum(sls) as sls

from tmp1

group by dist,ttype

order by dist,ttype,sls descending;

quit;

data rank_dist_nation;
set rank_dist_before;
by dist ttype descending sls ;
if first.dist then rank = 0;
Rank+1;
retain rank;
run;
Ask a Question
Discussion stats
  • 5 replies
  • 514 views
  • 0 likes
  • 4 in conversation