topic Re: Ranking in SAS Programming

Ranking

SASPhile — Tue, 27 Jul 2010 16:08:20 GMT

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;

Re: Ranking

Cynthia_sas — Tue, 27 Jul 2010 16:24:37 GMT

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.

Re: Ranking

Doc_Duke — Tue, 27 Jul 2010 16:26:32 GMT

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;

Re: Ranking

SASPhile — Tue, 27 Jul 2010 16:27:01 GMT

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

Re: Ranking

SASPhile — Tue, 27 Jul 2010 16:31:13 GMT

1 is assigned to all rows.

Re: Ranking

deleted_user — Tue, 27 Jul 2010 18:47:26 GMT

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;