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 |
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 |
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 |
You did it! It works perfectly. Thank you so much.
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;
Thank you very much for the solution. It works as well. It is really nice to have the help from the forum.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.