代码之家 › 专栏 › 技术社区 › Maz

SAS中列数的动态排序

sas

Maz · 技术社区 · 6 年前

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 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;

我的问题是,每次都有许多动态的因素。也就是说,在ordinal函数中有一个动态列表和一个ScoreRankX,它将上升到分数计数。

我试着这样做作为开始:

%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;

我可以更改每段代码开头的%let语句,但我正在尝试使排名部分更加自动化。你知道如何改进我正在编写的上述代码吗?目前,它输出错误,只有最后一列和“i”(即使我有do循环?)。

非常感谢您的帮助。

2 回复 | 直到 6 年前

data _null_ 6 年前

我想这就是你想要的。如果你想用不同的方式命名RANK变量,你可以像我在另一个答案中所做的那样使用EXPAND\u VARLIST。那么您就不需要&号码。

%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;

data _null_ 6 年前

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;

宏展开变量列表

%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;