turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Analytics
- /
- Stat Procs
- /
- Grouping data

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-22-2012 07:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 11:50 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 07:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 12:38 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 08:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 12:52 PM

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**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 01:12 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 01:50 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 01:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 03:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 03:45 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2012 11:50 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-24-2012 10:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-24-2012 10:55 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-26-2012 07:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-26-2012 09:21 AM

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