Solved
Contributor
Posts: 71

# Grouping data

How can I create subgroups in a large dataset so that all subgroups are approximately equal as measured by a specific variable? E.g to create X number of subgroups that have the same average for the variable Y. Would there be a clustering procedure to do that?

Thanks

Accepted Solutions
Solution
‎03-23-2012 11:50 PM
Posts: 5,056

## Re: Grouping data

I simulated 100 replicates of 2000 normal variates and compared the mean variability, the variance range and the size of 8 subgroups formed by two methods

PGperm is my permutation of Y-adjacent observations

SDrand is Steve's random allocation

Here is the complete test, followed by the results :

data randomY;
call streaminit(283219);
do rep = 1 to 100;
do i = 1 to 2000;
y = rand("NORMAL");
output;
end;
end;
run;

proc sort data=randomY; by rep y; run;

data PGgroups(drop = _seed _g1-_g8);
array _g{8} _g1-_g8;
retain _g (1:8);
set randomY nobs=_nobs;
if _n_ <= 8*floor(_nobs/8);
_seed = 8243959;
if mod(_n_-1, 8) = 0 then call ranperm(_seed, of _g1-_g8);
subGroup = _g(1 + mod(_n_-1, 8));
run;

data SDgroups;
set randomY;
if _n_ = 1 then call streaminit(1);
subGroup = ceil(8*rand("UNIFORM"));
run;

title "Two balanced grouping methods";

proc sql;

create table PGmeans as

select rep, subGroup, count(*) as n, mean(y) as meanY, var(y) as varY

from PGgroups

group by rep, subGroup;

create table PGvars as

select rep, range(n) as rangeN, var(meanY) as varMeanY, range(varY) as rangeVarY

from PGmeans

group by rep;

create table SDmeans as

select rep, subGroup, count(*) as n, mean(y) as meanY, var(y) as varY

from SDgroups

group by rep, subGroup;

create table SDvars as

select rep, range(n) as rangeN, var(meanY) as varMeanY, range(varY) as rangeVarY

from SDmeans

group by rep;

select "PGperm" as method, mean(rangeN) as meanRangeN, mean(varMeanY) as meanVarMeanY, mean(rangeVarY) as meanRangeVarY

from PGvars

union

select "SDrand" as method, mean(rangeN) as meanRangeN, mean(varMeanY) as meanVarMeanY, mean(rangeVarY) as meanRangeVarY

from SDvars;

quit;

-------

Two balanced grouping methods

meanRange
method  meanRangeN  meanVarMeanY           VarY
--------------------------------------------------------------------------
PGperm                 0             0.000033      0.020417
SDrand            46.07               0.00396     0.263877

I think it is fair to conclude that PGperm formed subgroups that were more closely matched.

PG

PG

All Replies
Posts: 2,655

## Grouping data

Some questions first:

How big is the large dataset (i.e. how many observations)?

How many subgroups?

What does the distribution of the variable Y look like (i.e., normal, nearly normal, skewed, horribly skewed, etc.)?

How close do the means of the subgroups have to be?  Identical will be impossible, so some acceptable interval needs to be specified.

I am thinking of PROC SURVEYSELECT as a starting tool, but that will require knowing how many subgroups you want.

Good luck.

Steve Denham

Contributor
Posts: 71

## Grouping data

I have a data set with about 2000 observations to be split into 8 groups that would have similar (not identical) average Y. The variable Y is normally distributed.

I didn't explain clearly my initial question, I think Rick took it as having Y be similar within the groups, whilst I want the group average Ys to be similar. The similarity doesn't need to be too tight, if that helps.

Thanks,

Fethon

SAS Super FREQ
Posts: 3,842

## Re: Grouping data

As Steve says, it isn't clear what you want. One interpretation of your request is that you want to split Y into quantiles: if you want 10 groups use deciles of Y, if you want 4 groups, use quartiles, etc.  Each group has about the same number of observations, and similar values of Y (where "similar" doesn't necessarily mean the same, especially for the upper and lower quantiles.)  If you want to do this, you can use PROC RANK, which contains an example of doing this.

If you don't care whether there are an equal number of obs in each group, then probably clustering is a good idea.

Rick

SAS Super FREQ
Posts: 3,842

## Grouping data

I'm not sure why you want 8 groups if Y is normally distributed, but here's how you can do it with k-means clustering:

data N;

call streaminit(1);

do i = 1 to 2000;

Y = rand("Normal");

output;

end;

run;

proc fastclus data=N maxc=8 out=clus;

var Y;

run;

proc freq data=clus;

tables cluster;

run;

proc sgplot data=clus;

scatter x=cluster y=Y /group=Cluster ;

run;

Posts: 2,655

## Re: Grouping data

Here is a data step method, using Rick's data.  Note that the group sizes are not identical, ranging from 219 to 283

data N;

call streaminit(1);

do i = 1 to 2000;

Y = rand("Normal");

Y2 = rand("Uniform");

output;

end;

run;

data N2;

set N;

subgroup=ceil(Y2*8);

run;

proc glm data=N2;

class subgroup;

model Y=subgroup;

means subgroup/hovtest=bf;

quit;

The call to GLM tests for differences in means and variances between the 8 groups.  Getting the groups to exactly the same size is going to be difficult, but I am pretty sure a macro implementing PROC SURVEYSELECT exists for just that purpose.

Steve Denham

Hope this helps.

SAS Super FREQ
Posts: 3,842

## Re: Grouping data

Steve's solution is just a random split into 8 groups. If that's what you wanted, why mention Y at all? I thought you were interested in grouping on Y.

If your data are randomly distributed in the data set, you can also just use

subgroup = mod(_N_, 8);

but Steve's method is "safer" in case the data are sorted or autocorrelated.

Posts: 5,056

## Re: Grouping data

For something very simple, you could try using the similarity of adjacent observations in the sorted dataset, with a little permutation :

data sortedY;
do i = 1 to 2000;

y = rannor(-1);

output;

end;
run;

proc sort data=sortedY; by y; run;

data grouped(drop = _seed _g1-_g8);
array _g{8} _g1-_g8;
retain _g (1 2 3 4 5 6 7 8);
set sortedY nobs=_nobs;
if _n_ <= 8*floor(_nobs/8);
_seed = 8243959;
if mod(_n_-1, 8) = 0 then call ranperm(_seed, of _g1-_g8);
group = _g(1 + mod(_n_-1, 8));
run;

proc sql;
select group, mean(y) as meanY from grouped group by group;

PG

Tweaked by PG.

PG
Posts: 2,655

## Re: Grouping data

If you need an exact split, so that all subgroups have equal size, try this:

data N;
call streaminit(1);
do i = 1 to 2000;
Y = rand("Normal");
output;
end;
run;

proc plan ;
factors subgroup=8 ordered rep=250 random;
output out=sg;
run;

data sg;
set sg;
i=_n_;
run;

proc sort data=n;
by i;
run;
proc sort data=sg;
by i;
run;

data n2;
merge sg n;
by i;
run;

proc glm data=N2;
class subgroup;
model Y=subgroup;
means subgroup/hovtest=bf;
quit;

I am sure there are slicker ways of getting all the info together, but this is 'file the serial numbers off' of legacy code.

Steve Denham

SAS Super FREQ
Posts: 3,842

## Re: Grouping data

Or from your previous post, sort by Y2 (the random uniform variable) and then use the mod(_N_, 8) trick.

Solution
‎03-23-2012 11:50 PM
Posts: 5,056

## Re: Grouping data

I simulated 100 replicates of 2000 normal variates and compared the mean variability, the variance range and the size of 8 subgroups formed by two methods

PGperm is my permutation of Y-adjacent observations

SDrand is Steve's random allocation

Here is the complete test, followed by the results :

data randomY;
call streaminit(283219);
do rep = 1 to 100;
do i = 1 to 2000;
y = rand("NORMAL");
output;
end;
end;
run;

proc sort data=randomY; by rep y; run;

data PGgroups(drop = _seed _g1-_g8);
array _g{8} _g1-_g8;
retain _g (1:8);
set randomY nobs=_nobs;
if _n_ <= 8*floor(_nobs/8);
_seed = 8243959;
if mod(_n_-1, 8) = 0 then call ranperm(_seed, of _g1-_g8);
subGroup = _g(1 + mod(_n_-1, 8));
run;

data SDgroups;
set randomY;
if _n_ = 1 then call streaminit(1);
subGroup = ceil(8*rand("UNIFORM"));
run;

title "Two balanced grouping methods";

proc sql;

create table PGmeans as

select rep, subGroup, count(*) as n, mean(y) as meanY, var(y) as varY

from PGgroups

group by rep, subGroup;

create table PGvars as

select rep, range(n) as rangeN, var(meanY) as varMeanY, range(varY) as rangeVarY

from PGmeans

group by rep;

create table SDmeans as

select rep, subGroup, count(*) as n, mean(y) as meanY, var(y) as varY

from SDgroups

group by rep, subGroup;

create table SDvars as

select rep, range(n) as rangeN, var(meanY) as varMeanY, range(varY) as rangeVarY

from SDmeans

group by rep;

select "PGperm" as method, mean(rangeN) as meanRangeN, mean(varMeanY) as meanVarMeanY, mean(rangeVarY) as meanRangeVarY

from PGvars

union

select "SDrand" as method, mean(rangeN) as meanRangeN, mean(varMeanY) as meanVarMeanY, mean(rangeVarY) as meanRangeVarY

from SDvars;

quit;

-------

Two balanced grouping methods

meanRange
method  meanRangeN  meanVarMeanY           VarY
--------------------------------------------------------------------------
PGperm                 0             0.000033      0.020417
SDrand            46.07               0.00396     0.263877

I think it is fair to conclude that PGperm formed subgroups that were more closely matched.

PG

PG
Contributor
Posts: 71

## Grouping data

PG, Rick, Steve,

Thank you all for your valuable inputs, when I get back to work tomorrow (I'm on Australia time zone!) I'll try the PGperm solution, I think it'll give me what I want.

I'm designing an experiment so I want to have all 8 treatment groups starting with similar average Y (which is a variable representing process stability). Random allocation of groups did not give very similar group averages for Y, maybe my assumption of normally distributed Y was wrong.

Regards,

Fethon Naoum

Posts: 5,056

## Re: Grouping data

The performance of both methods will deteriorate slightly with skewed Y data. If you replace  y = rand("NORMAL") with  y = exp(rand("NORMAL")) in the test above, to simulate lognormal data instead of normal, the test results become:

Two balanced grouping methods (lognormal data)

meanRange
method  meanRangeN  meanVarMeanY          VarY
----------------------------------------------
PGperm           0      0.001695      5.017333
SDrand       46.07      0.019796      7.407866

PG

PG
Posts: 2,655

## Re: Grouping data

WOW!  I have been looking for something like this for years.  I am trashing old code immediately, and am incorporating this method.  Too often I have had to "re-randomize" due to differences in means or variances, and this minimizes the chances of those occurrences.

Thank you!

Steve Denham

Message was edited by: Steve Denham

SAS Super FREQ
Posts: 3,842

## Re: Grouping data

If I understand correctly, PG's method is a random assignment into one of 8 subsets.

He's comparing it to a sequential assignment of the sorted data into subgroups.

Yes, the random assignment should perform better.

But I think PG's method is statistically equivalent to assigning U=uniform(1), sorting on U, and assigning subgroup=mod(_N_,8);

The corresponding DATA step is simpler since it avoids arrays and IF/THEN statements.

Rick

🔒 This topic is solved and locked.