BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jeff_DOC
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

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
Jeff_DOC
Pyrite | Level 9

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

Ksharp
Super User

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;
Jeff_DOC
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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