DATA Step, Macro, Functions and more

Ranking across multiple variables

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Ranking across multiple variables

Good afternoon.

 

I am working in EG version 6.1. I have a sas dataset with a numeric key and multiple numeric variables (8). What I need to do is to rank them by highest variable value ranked as number 1, second highest as number 2, etc. In the case of a tie I need both tied scores to have the same value i.e. highest ranked as number one, first variable tie also ranked as number 1, second highest value ranked as number 2, etc. All ranking must be within each observation, not ranked over the entire dataset. So all ranking would be within observation 1 for observation 1, all ranking for observation 2 would be within observation 2, etc.

 

I would really appreciate any help anyone could give me.

 

Thanks in advance.

 

Jeff

 

HAVE                  
KEY VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8  
1 1 2 4 4 4 9 9 7  
2 1 2 3 4 5 6 7 8  
3 7 8 9 9 6 5 5 4  
4 4 9 9 7 7 8 5 1  
                   
WANT 1                  
KEY VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8  
1 6 5 3 3 3 1 1 2  
2 8 7 6 5 4 3 2 1  
3 3 2 1 1 4 5 5 6  
4 5 1 1 3 3 2 4 6  
                   
ALTERNATIVE                
KEY VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8 RANK
1 1 2 4 4 3 8 9 7 VAR6;VAR7; VAR8
2 1 2 3 4 5 6 7 8 VAR7; VAR8
3 7 8 9 9 6 5 5 4 VAR3; VAR4
4 4 9 9 7 7 8 5 1 VAR2; VAR3

Accepted Solutions
Solution
‎07-12-2016 07:11 PM
PROC Star
Posts: 1,760

Re: Ranking across multiple variables

Like this?

 

 

data HAVE ;
input KEY VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8;
cards; 
1 1 2 4 4 4 9 9 7   
2 1 2 3 4 5 6 7 8   
3 7 8 9 9 6 5 5 4   
4 4 9 9 7 7 8 5 1 
run;

data WANT;
  set HAVE;                           
  array VAR [8]  ;                     %* Value array;
  array OUT [8] ;                      %* Rank array ;
  call missing(MAX,R);                 %* Reset row max and current rank;
  do I=1 to 8;                         %* 8 iterations;
    if MAX ne max(of VAR[*]) then do;  %* If new maximum found  ;
      R+1;                             %*   then set new rank   ;
      MAX=max(of VAR[*]);              %*   and set new maximum;
    end;
    POS       = whichn(MAX,of VAR[*]); %* Find position of maximum;
    VAR[POS] = .;                      %* Remove maximum from input values;
    OUT[POS]  = R;                     %* Add rank to output values;
  end;
  keep KEY OUT:;
run;
proc print noobs; run; 

 

KEY OUT1 OUT2 OUT3 OUT4 OUT5 OUT6 OUT7 OUT8
1 5 4 3 3 3 1 1 2
2 8 7 6 5 4 3 2 1
3 3 2 1 1 4 5 5 6
4 5 1 1 3 3 2 4 6

View solution in original post


All Replies
Solution
‎07-12-2016 07:11 PM
PROC Star
Posts: 1,760

Re: Ranking across multiple variables

Like this?

 

 

data HAVE ;
input KEY VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8;
cards; 
1 1 2 4 4 4 9 9 7   
2 1 2 3 4 5 6 7 8   
3 7 8 9 9 6 5 5 4   
4 4 9 9 7 7 8 5 1 
run;

data WANT;
  set HAVE;                           
  array VAR [8]  ;                     %* Value array;
  array OUT [8] ;                      %* Rank array ;
  call missing(MAX,R);                 %* Reset row max and current rank;
  do I=1 to 8;                         %* 8 iterations;
    if MAX ne max(of VAR[*]) then do;  %* If new maximum found  ;
      R+1;                             %*   then set new rank   ;
      MAX=max(of VAR[*]);              %*   and set new maximum;
    end;
    POS       = whichn(MAX,of VAR[*]); %* Find position of maximum;
    VAR[POS] = .;                      %* Remove maximum from input values;
    OUT[POS]  = R;                     %* Add rank to output values;
  end;
  keep KEY OUT:;
run;
proc print noobs; run; 

 

KEY OUT1 OUT2 OUT3 OUT4 OUT5 OUT6 OUT7 OUT8
1 5 4 3 3 3 1 1 2
2 8 7 6 5 4 3 2 1
3 3 2 1 1 4 5 5 6
4 5 1 1 3 3 2 4 6
Contributor
Posts: 42

Re: Ranking across multiple variables

You did it! It works perfectly. Thank you so much.

Super User
Posts: 10,048

Re: Ranking across multiple variables

It is IML thing:

 

 

data HAVE ;
input KEY VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8;
cards; 
1 1 2 4 4 4 9 9 7   
2 1 2 3 4 5 6 7 8   
3 7 8 9 9 6 5 5 4   
4 4 9 9 7 7 8 5 1 
;
run;
proc iml;
use have;
read all var {key} into k;
read all var {VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8} into x[c=vnames ];
close;
want=j(nrow(x),8,.);
 do i=1 to nrow(x);
   want[i,]=ranktie(x[i,<>]-x[i,],'dense'); 
 end;
want=k||want;
create want from want[c=('KEY'||vnames)];
append from want;
close;
quit;
Contributor
Posts: 42

Re: Ranking across multiple variables

Thank you very much for the solution. It works as well. It is really nice to have the help from the forum.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 714 views
  • 0 likes
  • 3 in conversation