Proc sql添加列以订购获奖者

2 回答

• 0

  正确排序数据，然后使用FIRST / LAST / BY组处理 .

  proc sort data = have;
      by descending Total_Medals descending Gold descending Silver descending Bronze;
  run;
  
  data want; 
   set have;
  
   by descending Total_Medals descending Gold descending Silver descending Bronze;
  
   Rank = _n_;
  run;
  

  回复于 2024-05-16T09:35:32+08:00
• 0

  使用 first. 组中最后一个变量的 first. 评估来确定何时增加整体排名值 .

  * sample data;
  data have (keep=eventid teamid medal);
    length eventid teamid 8 medal $8;
    do eventid = 1 to 75;
      g = ceil(100 * ranuni(123));
      do until (s ne g);
        s = ceil(100 * ranuni(123));
      end;
      do until (b ne g and b ne s);
        b = ceil(100 * ranuni(123));
      end;
  
      teamid = g; medal = 'gold'; output;
      teamid = s; medal = 'silver'; output;
      teamid = b; medal = 'bronze'; output;
    end;
  run;
  
  * compute medal count for each team;    
  proc summary noprint data=have ;
    class teamid medal;
    ways 2;
    output out=stage2(drop=_type_);
  run;
  
  * pivot categorical medal counts into wide form;
  proc transpose data=stage2 out=stage3(drop=_name_);
    by teamid;
    var _freq_;
    id medal;
  run;
  
  * compute each medaling teams total medal count;
  data stage4;
    retain teamid total gold silver bronze; * sets order of variables in pdv;
    set stage3;
    total = sum (gold, silver, bronze);
  run;
  
  * sort descendingly by total and medal quality in preparation of rank assignment;
  proc sort data=stage4;
    by descending total descending gold descending silver descending bronze;
  run;
  
  * assign dense rank;
  data want;
    set stage4;
    by descending total descending gold descending silver descending bronze;
  
    if first.bronze then /* new combination detected */
      overall_rank + 1;
  run;
  

  回复于 2024-05-16T09:35:32+08:00