Esteemed Advisers:
This challenge, should you choose to accept it, involves ranking under a complex set of rules.
A variable number of Players are assigned to Groups according their "Old Rank" order. The top four finishers in each group are awared 1st through 4th place. In some Groups there are more than 4 Players but those who did not finish in the top 4 are designated as DNF.
The desire is re-rank all of the Players in preparation for seeding the next contest. The attached code illustrates the challenge. The ranking rules are explained in the output. All is going according to plan in the output down to Observation 17 (Rob). Player Rob in Group 3 has a Result of 9 (DNF) and his rank should be below (numerically higher i.e. 18) that of the 4th place finisher Pat (17) in Group 3. Players Tom, Uli and Vic in Group 4 should be ranked 19,20,21 respectively. This being the end of the roster, the 4th Place Player, Vic, should be ranked behind the Uli, the 3rd place Player.
The code is divided into four parts:
Part I sets up a temporary array;
Part II Ranks Group 1 by a special set of rules
Part III Seeks to Rank remaining Groups by using BY GROUP Statment.
Part IV Print Ranking Chart
In the attached code that produced the result, my hunch is that the problem lies with how to reset the variable DNF to 0 when a new Group is processed by the BY Statement. I tried moving it to several locations but to no avail. I suspect I'm caught in a By Group do loop de loop.
As usual the Secretary will disavow any knowledge of your actions. However, if you succeed, I will be eternally grateful.
Regards,
Gene
Data Scores;
infile datalines;
input Group Player $3. Result OldRank;
datalines;
1 Abe 1 1
1 Bip 2 2
1 Cas 3 3
1 Dan 4 4
2 Elk 1 5
2 Fin 2 6
2 Gip 3 7
2 Han 4 8
2 Ian 9 9
2 Jak 9 10
2 Ken 9 11
2 Len 9 12
3 Min 1 13
3 Ned 2 14
3 Opi 3 15
3 Pat 4 16
3 Rob 9 17
4 Sal 1 18
4 Tom 2 19
4 Uli 3 20
4 Vic 4 21
;
run;
/*PART I: Create a Temporary Array*/
proc sort data=scores;
by group result;
run;
data RankingChart;
Retain Rank4 LastRank DNFRank;
length explanation $100;
/*Create temporary arrays for players, rankings and results*/
array PlayerName(30) $3 _temporary_;
array Chart(3,30) _temporary_;
if _n_=1 then do i=1 to nobs;
set scores nobs=nobs;
PlayerName(i)=Player;
Chart(1,i)=Group;
Chart(2,i)=Result;
Chart(3,i)=.;/*Placeholder for NewRank*/
end;
/*PART II: Rank Group 1*/
/*Actions taken based on result within group 1 (1st,2nd,3rd or 4th place) or
if Player Did Not Finish (DNF) (Result=9)*/
set scores nobs=nobs;
by Group;
/*Special Ranking Rules for Group 1*/
/*Rule for 1st Place Winner Group 1*/
if group=1 and result=1 then
do;
explanation='Winner of Group 1: New Rank=1';
/*Identify player*/
do i=1 to nobs;
if Player=PlayerName(i) then
do;
Chart(3,i)=1;
NewRank = Chart(3,i);
LastRank=1;
end;
end;
end;
/*Rule for 2nd Place Player Group 1*/
if group=1 and result=2 then
do;
explanation='2nd Place Player ahead of all other players in Group 1';
/*Identify Player*/
do i= 1 to nobs;
if player=playername(i) then
do;
/* NewRank is LastRank +1*/
Chart(3,i)=LastRank+1;
NewRank=Chart(3,i);
LastRank=NewRank;
end;
end;
end;
/*Rule for 3rd Place Player Group 1*/
if group=1 and result=3 then
do;
explanation='3rd Place:Moves ahead of 4th place player and behind 2nd Place in Group 1';
/*Identify player*/
do i=1 to nobs;
if Player=PlayerName(i) then
do;
/* NewRank is LastRank +1*/
Chart(3,i)=LastRank+1;
NewRank=Chart(3,i);
LastRank=NewRank;
end;
end;
end;
/*Rule for 4th Place Player Group 1*/
if group=1 and result=4 then
do;
explanation='4th Place player must rank below winner of next lower group.';
/*Identify player*/
do i=1 to nobs;
if Player=PlayerName(i) then
do;
/* NewRank is LastRank +2 to be below winner of next lower group*/
Chart(3,i)=LastRank+2;
NewRank=Chart(3,i);
LastRank=NewRank;
Rank4=NewRank;
end;
end;
end;
/*Rule for Player that did not finish*/
/* If there is no DNF Player in Group 1 then DNFRank is set to missing*/
if group=1 and result=9 then
do;
explanation="DNF: Moves below 4th place finisher of Group 1 and winner of next lower group";
/*Identify NewRank of 4th place player*/
do i=1 to nobs;
if (Chart(1,i)=1 and chart(2,i)=4) then Rank4=chart(3,i);
end;
do i=1 to nobs;
if Player=PlayerName(i) then
do;
/* NewRank is Rank4 +1*/
Chart(3,i)=Rank4+1;
NewRank=Chart(3,i);
LastRank=NewRank;
DNFRank=NewRank;
end;
end;
end;
/*Part III: Rank Remaining Groups*/
/*Actions taken based on result within remaining Groups(1st,2nd,3rd or 4th place) or
if Player DNF (Result=9) */
set scores nobs=nobs;
by Group;
retain DNF 0;
/*Rule for 1st Place Winner of Group*/
if 1<group<5 and result=1 then
do;
explanation='Winner of Group: New Rank is above Place 4 of higher Group';
/*Identify player 4th place player in higher group*/
do i=1 to nobs;
if (Chart(1,i)=group-1 and Chart(2,i)=4) then
LastRank=Chart(3,i);
end;
/*Identify player*/
do i=1 to nobs;
if Player=PlayerName(i) then
do;
Chart(3,i)=Rank4-1;
NewRank = Chart(3,i);
end;
end;
end;
/*Rule for 2nd Place Winner of Group*/
if 1<group<5 and result=2 then
do;
explanation='2nd Place: Move behind maximum ranked player in higher group (including DNFs).';
/*Identify player*/
do i=1 to nobs;
if Player=PlayerName(i) then
do;
If DNFRank=. then DNFRank=0;
if LastRank GE DNFRank then
Chart(3,i)=LastRank+1;
else
Chart(3,i)=DNFRank+1;
NewRank=Chart(3,i);
LastRank=NewRank;
end;
end;
end;
/*Rule for 3rd Place Winner of Group*/
if 1<group<5 and result=3 then
do;
explanation='3rd Place; Move behind 2nd place player of group.';
/*Identify player*/
do i=1 to nobs;
if Player=PlayerName(i) then
do;
Chart(3,i)=LastRank+1;
NewRank = Chart(3,i);
Lastrank=NewRank;
end;
end;
end;
/* Rule for 4th Place Winner of Group */
if 1<group<5 and result=4 then
do;
explanation='Must rank below winner of next lower group.';
/*Identify player */
do i=1 to nobs;
if Player=PlayerName(i) then
do;
Chart(3,i)=LastRank+2;
NewRank = Chart(3,i);
LastRank=NewRank;
Rank4=NewRank;
end;
end;
end;
/*Rule for DNF Player(s)*/
if 1<group<5 and result=9 and NewRank=. then
do;
explanation="DNF: All move below 4th place finisher in group";
/*count number of members in group that meet condition*/
do i=1 to nobs;
if Player=PlayerName(i) then
DNF=DNF + 1;
do;
/* NewRank is Rank of 4th Place player + 1*/
Chart(3,i)=LastRank+DNF;
NewRank=Chart(3,i);
DNFRank=NewRank;
end;
end;
end;
run;
proc sort data= rankingchart;
by oldrank;
proc print data=rankingchart;
Title "New Ranking of Players in Test Case (sorted by New Rank)";
var Group Player Result OldRank NewRank Explanation;
run;
