Hello @vicky07,
Do you have a SAS/OR license? I don't, but I'm sure the optimizers available there could produce an excellent solution.
Otherwise, is SAS/STAT available at your site? Then you could let PROC SURVEYSELECT create a number of random group assignments (with virtually equal group sizes, in your case: 67, 67, 68, 68, 68) and then pick the assignment with the smallest variance between the five group means of balance. Of course, increasing the number of test assignments will tend to improve (i.e. decrease) the minimum variance.
With your sample data (read with the below INPUT statement, though)
input emp $ seq_no balance;
and the following steps
/* Create 10000 random group assignments */
proc surveyselect data=have(drop=emp) out=test rep=10000 groups=5 seed=3141592;
run; /* 10000*338 = 3380000 obs. */
/* Compute mean balance for each group */
proc summary data=test nway;
by replicate;
class groupid;
var balance;
output out=stats mean=mbal;
run; /* 10000*5 = 50000 obs. */
/* Compute variance of the 5 mean balances for each assignment */
proc summary data=stats;
by replicate;
var mbal;
output out=mstats var=var_mbal;
run; /* 10000 obs. */
/* Determine the assignment with minimum variance */
proc summary data=mstats;
var var_mbal;
output out=opt min= minid(var_mbal(replicate))=repopt;
run; /* 1 obs. */
proc print data=opt;
run; /* var_mbal=460.768, repopt=2378 */
/* Select the "optimum" assignment */
data want(drop=r: groupid);
length emp $2;
if _n_=1 then set opt(keep=r:);
set test;
if replicate=repopt;
emp=cats('J',groupid);
run; /* 338 obs. */
/* Final check of group sizes and group means */
proc means data=want maxdec=2;
class emp;
var balance;
run;
I obtained:
Analysis Variable : balance
N
emp Obs N Mean Std Dev Minimum Maximum
---------------------------------------------------------------------------------
J1 67 67 6302.88 3782.57 786.69 17987.13
J2 67 67 6256.40 5918.76 718.39 45464.87
J3 68 68 6297.45 3580.75 551.79 14225.61
J4 68 68 6310.54 3343.95 1304.99 14992.90
J5 68 68 6281.56 3942.35 938.83 18236.87
---------------------------------------------------------------------------------
So, the five means differ by <1%. Would this be close enough? If not, you could increase rep= or try different seed= values.
If only Base SAS is available to you, you'll need to replace the PROC SURVEYSELECT step by suitable DATA and possibly PROC SORT steps. Please don't hesitate to ask if you need help with that.
ADDENDUM
A little bird told me that the convenient GROUPS= option of PROC SURVEYSELECT was not yet available in SAS version 9.3. Sorry that I hadn't checked this in the old documentation. For @vicky07 and all other users still on v9.3 (or even v9.2) and also for users without access to SAS/STAT here comes a workaround (as mentioned above): Simply replace the PROC SURVEYSELECT step by the code below.
/* Define number of groups and number of replicates */
%let ngroups=5;
%let nrep=10000;
/* Create list of group sizes and group IDs (1, 2, ...) for array definition */
data _null_;
length c $999;
call symputx('_n', n);
r=mod(n,&ngroups);
s=floor(n/&ngroups);
do i=1 to &ngroups-r;
c=catx(' ',c,cats(s,'*',i));
end;
do i=i to &ngroups;
c=catx(' ',c,cats(s+1,'*',i));
end;
call symputx('_gs', c);
stop;
set have nobs=n;
run;
%put Group sizes * group IDs: &_gs;
/* Create &nrep random group assignments */
data test;
array _g[&_n] _temporary_ (&_gs);
_iorc_=31415927; /* random seed */
do replicate=1 to &nrep;
call ranperm(_iorc_, of _g[*]);
do _n_=1 to &_n;
set have(drop=emp) point=_n_;
groupid=_g[_n_];
output;
end;
end;
stop;
run; /* &nrep*&_n obs. */
Due to the different approach and the random nature of the group assignments the results are not exactly the same as before. For @vicky07's sample data the new results are even better in terms of variance reduction: var_mbal=297.134 for repopt=1608.
(Okay, I tested a few more random seeds and was lucky to find a good one ... 🙂 )
Analysis Variable : balance
N
emp Obs N Mean Std Dev Minimum Maximum
---------------------------------------------------------------------------------
J1 67 67 6297.45 3718.49 786.69 14225.61
J2 67 67 6293.21 6003.82 718.39 45464.87
J3 68 68 6299.85 3591.68 551.79 15990.48
J4 68 68 6299.39 3712.96 916.52 18236.87
J5 68 68 6259.38 3512.09 950.52 15118.05
---------------------------------------------------------------------------------
