topic Re: Ranking across multiple variables in SAS Programming

Ranking across multiple variables

Jeff_DOC — Tue, 12 Jul 2016 21:32:05 GMT

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

Re: Ranking across multiple variables

ChrisNZ — Tue, 12 Jul 2016 22:49:55 GMT

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

Re: Ranking across multiple variables

Jeff_DOC — Tue, 12 Jul 2016 23:12:34 GMT

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

Re: Ranking across multiple variables

Ksharp — Wed, 13 Jul 2016 02:24:22 GMT

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;

Re: Ranking across multiple variables

Jeff_DOC — Wed, 13 Jul 2016 15:02:27 GMT

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