Ranking

[SASPhile]

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;

[Cynthia_sas]

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.

[SASPhile]

I tried Proc rank,but it gives me the same rank for all rows.

[Doc_Duke]

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;

[SASPhile]

1 is assigned to all rows.

[deleted_user]

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;