Proc-sql将列添加到订单获胜者

Question

我需要你的帮助，我有一个练习，要求按奖牌总数排序获胜者，如果两个国家的奖牌数量相同，那么我需要按顺序比较3列(这是奖牌类型(。

所以我有

sum(3columns) as total
order total desc, col1 desc , col2 desc , col3 desc

然后我把它保存为表，并添加了单调((作为获胜者的位置(1 2 3 4…(但我得到了三个团队，他们的总成绩和三列中的值相同所以他们必须排在第25位，但我正在努力做到这一点。

提前感谢

Answer 1

对数据进行正确排序，然后使用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;

Answer 2

使用by组中最后一个变量的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;