Last week I submitted a question on ranking of scores and received a very helpful response from s_lassen. I subsequently learned that the ranking rules had changed and I am now back with a request for additional help.
The attached code produces output that, I hope, illustrates the issues. The column labeled WantedRank is the desired outcome of the Ranking Code which is labeled NewRank. The Agreement column indicates agreement between the two. The Explanation column is the rule that governs the ranking.
Players are divided into groups based on the previous ranking (OldRank). There are 4 active players (Registration=1) in each group but there can be several Inactive players (Registration=0 and Score=0) in any group.
The ranking rule for the 4th place player when there are Inactive players in the group is the stumbling block. The rule is that the 4th place player in the group must move down in rank below any Inactive player in the group AND below the 1st place player in the lower group. I haven't figured out how to capture this rule in the code.
Thanks in advance for taking the time and effort to consider this problem. Let me know if you have any questions.
Regards,
Gene
data work.RANKINGDATA;
infile datalines dsd truncover;
input OldRank:BEST12. Player:$26. Registration:BEST12. WantedRank:BEST12. Scores:BEST12. Groups:BEST12.;
format OldRank BEST12. Registration BEST12. WantedRank BEST12. Scores BEST12. Groups BEST12.;
datalines4;
1,Jo Comstock,1,1,42,1
2,Andrea Dilger,1,2,39,1
3,Sonja Drinkwalter,1,3,38,1
4,Lynn Manns,1,5,29,1
5,Linda van Son,1,6,38,2
6,Nancy Popenhagen,1,7,36,2
7,Ann Elliott,0,8,0,2
8,Jan Raugust,0,9,0,2
9,Sara Meline,1,4,40,2
10,Renee Salko,0,10,0,2
11,Karen Weldon,0,11,0,2
12,Georgia Graeff,1,13,30,2
13,Patti Holden,1,12,40,3
14,Cris Silkman,1,15,35,3
15,Jane Wood,1,14,36,3
16,Jennifer Roberts,0,16,0,3
17,Pat Chernow,1,18,21,3
18,Roberta Diles [sm],1,17,44,4
19,Norma Lozano,0,19,0,4
20,Tammy Dana-Bashian,0,20,0,4
21,Lori McLinton,1,21,43,4
22,Beth Weaver,0,22,0,4
23,Katie Claypool,0,23,0,4
24,Cathy Dosch,1,24,38,4
25,Michelle Dileonardo,1,26,36,4
;;;;
/*Code for Ranking Ladder Hierarchy Pickleball Results*/
/*Developed from recommended approach by s_lassen on the SAS Communities User Group*/
/*Create Rank of Scores by Group*/
proc rank data=rankingdata out=rank1 descending ties=low;
by groups;
var scores;
ranks Result;
run;
/*If player did not play then is Inactive and Result set to 9*/
data work.rank2;
set work.rank1;
if registration=0 and scores=0 then result=9;
run;
proc sort data=work.rank2;
by groups result;
run;
data regroup;
set rank2;
Length Explanation $150;
/* winner of lower group moves up to above 4th place in higher group */
/* move winner */
if result=1 and groups>1 then do;
group2=groups-1;
result2=4;
end;
else do;
/* make room for winner */
result2=result+(result>3);
group2=groups;
end;
/* Explanations */
if Groups=1 and Result=1 then
explanation='Winner of Group 1: New Rank=1';
If Groups NE 1 and Result=1 then
explanation='1st Place in Group: Ranks ahead of 4th Place higher Group';
If Result=2 then
explanation='2nd Place in Group: Ranks ahead of all players within Group';
if Result=3 then
explanation='3rd Place in Group: Ranks below 2nd Place in Group';
If Result=4 then
explanation='4th Place in Group: Ranks below winner of next lower Group and below any Inactive Players within Group';
if Result=9 then
explanation='Inactive: Can only maintain rank or go down in rank if a lower ranked player moves above them by another rule';
run;
proc sort data=regroup;
by group2 result2;
run;
data want;
set regroup;
Length Agreement $3;
NewRank=_N_;
drop group2 result2;
/*Differences in Rank*/
if WantedRank=NewRank then Agreement='Yes';
else Agreement='No';
run;
proc sort data=want;
by oldrank;
proc print data=want;
Title "Comparison of Manual Ranking with Ranking Code";
var Groups Player OldRank Registration Scores Result NewRank WantedRank Agreement Explanation;
run;
