No problem. The extended macro shown below contains two simple additional PROC steps, the second of which creates a dataset TOTAL_SCORES which is eventually merged with the other group-level data to form the result dataset.
I assumed that you have the score data in the form of a dataset with one observation per player. But it would be easy to create such an input dataset from, e.g., the sample data in your earlier post, if necessary.
Macro ITER contains new parameters DSSC and SCVARS to keep the names of the score input dataset and the score variables flexible.
/* Create test data */
data scores;
input p ScoreA ScoreB ScoreC;
cards;
1 100 200 30
2 10 20 30
3 5 10 15
4 2 4 30
5 10 20 30
6 10 20 30
7 5 10 30
8 2 4 30
9 100 200 30
10 100 200 30
;
data have;
input a $ p;
cards;
A 2
B 2
C 3
D 3
E 4
F 4
G 1
H 1
I 1
A 5
B 6
C 6
D 7
E 8
F 9
G 9
H 10
I 10
;
/* Macro to perform the iterative algorithm */
%macro iter( dsin =have /* input dataset linking players and assignments */
, dssc =scores /* input dataset with scores for each player */
, scvars=ScoreA ScoreB ScoreC /* list of score variables */
, dsout=want /* output dataset */
, v1=a /* variable name for assignment IDs */
, v2=p /* variable name for player IDs */
, d1=_ /* delimiter for final list of assignment IDs */
, d2=%str(,) /* delimiter for final list of player IDs */
);
%local i n1 n2;
%let i=1;
proc sort data=&dsin presorted;
by &v1;
run;
/* Assign initial group numbers g */
data t1;
set &dsin;
by &v1;
g+first.&v1;
run;
proc sql noprint; /* The sum of all group numbers */
select sum(g) into :n1 /* is used to detect changes */
from t1; /* after reassigning groups. */
quit;
%if &n1>=%sysfunc(constant(EXACTINT))
%then %put %str(WAR)NING: Sum of group numbers has become too large. Results may be incorrect!;
%do %until(&n1=&n2);
%let i=%eval(3-&i); /* toggle i between 1 and 2 */
proc sql noprint; /* If one player has been assigned */
create table t&i as /* multiple group numbers, they are */
select &v1, &v2, min(g) as g /* replaced by their minimum. */
from t%eval(3-&i) /* In the next iteration the same */
group by &&v&i; /* procedure is applied to assign- */
/* ment IDs rather than player IDs. */
select sum(g) into :n&i /* And so on, alternating, until */
from t&i; /* no reassignments occur, i.e., t1 */
quit; /* and t2 are equal up to sort order */
%end; /* and hence &n1=&n2. */
%do i=1 %to 2; /* Sorted lists of distinct assign- */
proc sql; /* ment and player IDs are prepared. */
create table u&i as
select distinct g, &&v&i
from t&i;
quit;
data r&i; /* Finally, the delimited lists of */
do until(last.g); /* IDs are created for reporting. */
set u&i;
by g;
length &&v&i..list $32767; /* length might be reduced carefully */
&&v&i..list=catx("&&d&i", &&v&i..list, &&v&i);
end;
drop &&v&i;
run;
%end;
proc sql; /* Scores are retrieved */
create table s0 as
select x.&v2, g, %sysfunc(translate(%cmpres(&scvars),%str(,),%str( )))
from t2 x left join &dssc y
on x.&v2=y.&v2;
quit;
proc summary data=s0 nway; /* Scores are summed within groups */
class g;
var &scvars;
output out=total_scores(drop=_:) sum=;
run;
/* Create result dataset */
data &dsout;
merge r1 r2 total_scores;
by g;
drop g;
label &v1.list='Assignment ID'
&v2.list='Players';
run;
%mend iter;
%iter;
proc print data=want noobs label;
run;
I'll be back tomorrow (CEST).
