topic Re: Grouping data in Statistical Procedures

Grouping data

PTD_SAS — Thu, 22 Mar 2012 23:09:25 GMT

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

Grouping data

SteveDenham — Fri, 23 Mar 2012 11:10:23 GMT

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

Re: Grouping data

Rick_SAS — Fri, 23 Mar 2012 12:54:02 GMT

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

Grouping data

PTD_SAS — Fri, 23 Mar 2012 16:38:31 GMT

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

Grouping data

Rick_SAS — Fri, 23 Mar 2012 16:52:26 GMT

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;

Re: Grouping data

PGStats — Fri, 23 Mar 2012 17:03:51 GMT

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;

Tweaked by PG.

Re: Grouping data

SteveDenham — Fri, 23 Mar 2012 17:12:55 GMT

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.

Re: Grouping data

Rick_SAS — Fri, 23 Mar 2012 17:50:07 GMT

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.

Re: Grouping data

SteveDenham — Fri, 23 Mar 2012 19:00:14 GMT

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

Re: Grouping data

Rick_SAS — Fri, 23 Mar 2012 19:45:19 GMT

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

Re: Grouping data

PGStats — Sat, 24 Mar 2012 03:50:17 GMT

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.

Grouping data

PTD_SAS — Sun, 25 Mar 2012 02:34:54 GMT

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

Re: Grouping data

PGStats — Sun, 25 Mar 2012 02:55:43 GMT

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

Re: Grouping data

SteveDenham — Mon, 26 Mar 2012 11:59:38 GMT

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

Re: Grouping data

Rick_SAS — Mon, 26 Mar 2012 13:21:03 GMT

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

Grouping data

PGStats — Mon, 26 Mar 2012 14:48:40 GMT

Rick, what you describe as statiscally equivalent to "PG's method" in your second paragraph is actually SD's method. PG's method differs in that it does a random assignment to groups within quantiles of the distribution. Thus, similar values of Y are assigned uniformly among the groups. I did the tests above to explore the difference between local (PG) and global (SD) shuffling.

Grouping data

SteveDenham — Mon, 26 Mar 2012 17:13:27 GMT

Key to note that the 'global' method I used did NOT sort before assigning subgroups, so Rick's comment that the two are equivalent should stand, if you sort on U to begin with.

In any case, I learned a lot in this thread. I hope the OP did as well.

Steve Denham

Grouping data

PTD_SAS — Mon, 26 Mar 2012 21:37:49 GMT

I'm very glad that I posted my initial question, I learned a lot!

I used to do just randomisation to assign test groups (for DOE) but that not always resulted in groups with similar averages or variance for specific variables. PG's method works very well, I tested it on various datasets with real-time process data.

Thanks to all of you!

Fethon

Grouping data

SteveDenham — Tue, 27 Mar 2012 10:57:16 GMT

And after a bit of rest and time, I finally realized what PGStat's method was--a block randomization. Assign observations to a block based on some value (ranking phase), and randomize (permutation phase) within the block. This will almost always lead to more homogeneity within block, and hence the entire schema, when examined over all blocks.

Steve Denham

Grouping data

PGStats — Tue, 27 Mar 2012 18:31:09 GMT

I posted a that explains the test above and also includes yet another assignment method that gives near perfect balance, plus a couple of references. Following Steve's comment on a proper name for the method that I proposed, I could search further on the net and find that the topic is definitely not a recent one and gets a lot more complicated when one tries to balance many factors at the same time.