Ranking dynamic number of columns in SAS

user_6010478 0
Viewed 333

I have a dataset with a Key and a number of scores for different factors (A,B,C,D...). It looks something like this:

data scores;
input KEY A B C D E F G H;
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;

I'm trying to get a ranking of each factor that looks something like the output of this:

proc sql;
create table scorerank as
select   *
        ,(ordinal(1,A,B,C,D,E,F,G,H)) as ScoreRank1
        ,(ordinal(2,A,B,C,D,E,F,G,H)) as ScoreRank2
        ,(ordinal(3,A,B,C,D,E,F,G,H)) as ScoreRank3
        ,(ordinal(4,A,B,C,D,E,F,G,H)) as ScoreRank4
        ,(ordinal(5,A,B,C,D,E,F,G,H)) as ScoreRank5
        ,(ordinal(6,A,B,C,D,E,F,G,H)) as ScoreRank6
        ,(ordinal(7,A,B,C,D,E,F,G,H)) as ScoreRank7
        ,(ordinal(8,A,B,C,D,E,F,G,H)) as ScoreRank8
from scores;
quit;

My problem is that there is a dynamic number of factors every time. Which means, a dynamic list within the ordinal function and a ScoreRankX that goes up to the count of Scores.

I have tried to do this as a start:

%let num = 8;
%let factors = A,B,C,D,E,F,G,H;

data datarank;
set scores;
do i = 1 to &num.;
ScoreRank&num. = (ordinal(&num.,&factors.));
end;
run;

I'm OK to change the %let statements at the start of each code, but i'm trying to make the ranking part more automatic. Any idea of how I can improve the above code that I am working on? Currently it outputs incorrectly with just the last rank and "i" (even though I have the do loop in?).

Any help much appreciated.

sas
2 Answers

For ranking across the rows I think this is what you want. If you want to name the RANK variables differently you could use EXPAND_VARLIST as I did in my other answer. Then you don't need &num.

%let num = 8;
data datarank;
   set scores;
   array score[*] a--h;
   array Rank[&num];
   do i = 1 to dim(rank);
      Rank[i] = ordinal(i,of score[*]);
      end;
   drop i;
   run;
proc print;
   run;

enter image description here

user_21962200

Are you ranking the columns or the rows? ORDINAL function ranks rows. Seems like you should use PROC RANK and rank columns. The default for PROC RANK is to use the same variables for the ranks which works well most of the time but you seem to want new names so I include method for that.

data scores;
   input KEY A B C D E F G H;
   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 print;
   run;
%let ranks=%expand_varlist(data=ranks,var=a-numeric-h,expr=cats('Rank_',_name_));
proc rank out=ranks ties=mean;
   var a--h;
   ranks &ranks;
   run;
proc print;
   run;

enter image description here

Macro EXPAND_VARLIST

%macro
   expand_varlist /*Returns an expanded variable list and optionally creates an indexed data set of variable names*/
      (
         data  = _LAST_,            /*[R]Input data*/
         var   = _ALL_,             /*[R]Variable List expanded*/
         where = 1,                 /*[R]Where clause to subset OUT=, useful for selecting by a name suffix e.g. where=_name_ like '%_Status'*/
         expr  = nliteral(&name),   /*[R]An expression that can be used to modify the names in the expanded list*/
         keep  = ,                  /*[O]Keep data set option for DATA=*/
         drop  = ,                  /*[O]Drop data set option for DATA=*/
         out   = ,                  /*[O]Output data indexed by _NAME_ and _INDEX_*/
         name  = _NAME_,            /*[R]Name of the variable name variable in the output data set*/
         label = _LABEL_,           /*[R]Name of the variable name label variable in the output data set*/
         index = _INDEX_,           /*[R]Name of the variable index variable in the output data set*/
         dlm   = ' '                /*[R]List delimiter*/
      );
   %local m i;
   %let i=&sysindex;
   %let m=&sysmacroname._&i;
   %do %while(%symexist(&m));
      %let i = %eval(&i + 1);
      %let m=&sysmacroname._&i;
      %end;
   %put NOTE: &=m is a unique symbol name;
   %local rc &m code1 code2 code3 code4;
   %if %superq(out) ne %then %let code3 = %str(data &out(index=(&index &name)); set &out; &index+1; run;);
   %else %do;
      %let out=%str(work._deleteme_);
      %let code3 = %str(proc delete data=work._deleteme_; run;);
      %end;
   %let code1 = %str(options notes=0; proc transpose name=&name label=&label data=&data(obs=0 keep=&keep drop=&drop) out=&out(where=(&where)); var &var; run;);
   %let code2 = %str(proc sql noprint; select &expr into :&m separated by &dlm from &out; quit;);
   %let code4 = %str(options notes=1;);
   %let rc=%sysfunc(dosubl(&code1 &code2 &code3 &code4));
&&&m.
   %mend expand_varlist;
user_21962200
Related