Below is a more elementary solution, based on an iterative algorithm, developed independently of @Ksharp's solution.
Limitation: If the sum of group numbers g becomes too large (about 9.0E15 on Windows systems), a warning is issued and the criterion "&n1=&n2" should be replaced, e.g., by "equality of t1 and t2 up to sort order" (requires amended code).
@Krislynn742005: So, you can test both solutions and compare the run times as well as the results (please note, however, that the sort order of the lists is likely to differ). Please let us know how they performed on the real data.
/* Create test data */
data have;
input a p;
cards;
1 7
2 7
10 11
10 12
10 13
10 15
10 19
10 20
24 7
163 8
163 9
163 11
163 14
164 10
164 15
164 16
164 17
164 21
174 11
1063 7
1356 7
3080 6
3099 5
3099 6
3099 18
3099 23
3112 6
3112 18
4947 1
4947 2
4947 3
4947 4
4947 22
4947 24
4948 2
4948 22
4948 24
4949 3
4950 1
4950 4
;
/* Macro to perform the iterative algorithm */
%macro iter( dsin =have /* input dataset */
, 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;
/* Create result dataset */
data &dsout;
merge r1 r2;
by g;
drop g;
label &v1.list='Assignment ID'
&v2.list='Players';
run;
%mend iter;
%iter;
proc print data=want noobs label;
run;
Edit: Introduced macro parameters replacing previously hardcoded local macro variables and added sort step at the beginning.
