topic Re: rank scores by data step in SAS Procedures

rank scores by data step

Geo- — Sun, 09 Sep 2018 14:23:08 GMT

To rank scores by data step, If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Re: rank scores by data step

Jagadishkatam — Sun, 09 Sep 2018 14:32:05 GMT

data have;input Id  Score ;
cards;
 1  3.50 
2  3.65
3  4.00
4  3.85
5  4.00
6  3.65 
;

proc sort data=have;
by descending score;
run;

data want;
set have;by descending score;
retain rank;
if first.score then rank+1;

run;

Re: rank scores by data step

novinosrin — Sun, 09 Sep 2018 16:46:12 GMT

Hi @Geo- Is it some kind of exclusive datastep challenge? If so, I like it

data have;
input Id  Score ;
cards;
 1  3.50 
2  3.65
3  4.00
4  3.85
5  4.00
6  3.65 
;


data _null_;
call symputx('n',n);
set have nobs=n;
stop;
run;

data want;
do n=1 by 1 until(lr);
retain rank score;
set have end=lr;
array t(&n) ;
array k t&n-t1 ;
t(n)=score;
end;
call sortn(of k[*]);
score=t(1);
rank=1;
output;
do n=2 to dim(t);
if t(n) ne t(n-1) then rank+1;
score=t(n);
output;
end;
keep  score rank;
run;

Re: rank scores by data step

novinosrin — Sun, 09 Sep 2018 17:17:11 GMT

data have;
input Id  Score ;
cards;
 1  3.50 
2  3.65
3  4.00
4  3.85
5  4.00
6  3.65 
;

data want;
if 0 then set have(rename=(score=_score));
   dcl hash H (dataset:'have(rename=(score=_score))',ordered:'d', multidata:'y') ;
   h.definekey  ("_score") ;
   h.definedata ('_score') ;
   h.definedone () ;
dcl hiter i('h');
do while(i.next()=0);
if _score ne score then rank+1;
score=_score;
output;
end;
stop;
keep rank score;
run;