BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PTD_SAS
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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, 😎 = 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

View solution in original post

24 REPLIES 24
SteveDenham
Jade | Level 19

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

PTD_SAS
Obsidian | Level 7

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

Rick_SAS
SAS Super FREQ

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

Rick_SAS
SAS Super FREQ

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;

SteveDenham
Jade | Level 19

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.

Rick_SAS
SAS Super FREQ

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.

PGStats
Opal | Level 21

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, 😎 = 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
SteveDenham
Jade | Level 19

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

Rick_SAS
SAS Super FREQ

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

PGStats
Opal | Level 21

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, 😎 = 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
PTD_SAS
Obsidian | Level 7

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

PGStats
Opal | Level 21

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
SteveDenham
Jade | Level 19

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

Rick_SAS
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 24 replies
  • 5106 views
  • 6 likes
  • 4 in conversation