topic Re: assign observations into groups based on variables in SAS Programming

assign observations into groups based on variables

leex1514 — Thu, 10 Feb 2022 20:58:25 GMT

hello folks,

I am trying to assign each observation into 3 groups based on two variables (N size and score) so that each group has similar values in two variables ( e.i. average N size and score per group is similar). I have a small size mock up data shown below ( of course my real data is longer). How should I write a code for group assignment?

data1

id	N size	score
A	10	131
B	30	127
C	4	109
D	1	110
E	9	125
F	51	127
G	22	119
H	42	130
I	7	100

Re: assign observations into groups based on variables

PaigeMiller — Thu, 10 Feb 2022 22:24:58 GMT

PROC FASTCLUS

PROC CLUSTER with method = AVERAGE

Re: assign observations into groups based on variables

Ksharp — Fri, 11 Feb 2022 03:53:16 GMT

I think it is more like a OR problem.

Post it at OR froum

https://communities.sas.com/t5/Mathematical-Optimization/bd-p/operations_research

and calling out @RobPratt

here is @Rick_SAS blog about it ,maybe could give you a help.

https://blogs.sas.com/content/iml/2017/05/01/split-data-groups-mean-variance.html

Re: assign observations into groups based on variables

RobPratt — Fri, 11 Feb 2022 04:59:33 GMT

Please see my answer to this earlier question for an approach that uses PROC OPTMODEL.

Re: assign observations into groups based on variables

Ksharp — Fri, 11 Feb 2022 05:25:28 GMT

I took code from @Rick_SAS . But best choice is SAS/OR .


data Units;
infile cards expandtabs;
input id $  size	score;
cards;
A	10	131
B	30	127
C	4	109
D	1	110
E	9	125
F 	51	127
G	22	119
H	42	130
I	7	100
;


%let NumGroups =3;           /* number of treatment groups */
data Treatments;
do Trt = 1 to &NumGroups;    /* Trt is variable that assigns patients to groups */
   output;
end;
run;

%let Var = size score;      /* names of multiple covariates */
proc optex data=Treatments seed=97531 coding=orthcan;
   class Trt;
   model Trt;              /* specify treatment model */
   blocks design=Units;    /* specify units */
   model &Var;             /* multiple covariate means will be approx same */
   output out=Groups;      /* merged data: units assigned to groups */
run;

proc means data=Groups mean std;
  class Trt;
  var &Var;
run;

Re: assign observations into groups based on variables

leex1514 — Mon, 14 Feb 2022 21:14:55 GMT

It does not look like either method brings me similar average values in each variable. Is there any method how to assign each observation into 5 groups so that each group has similar average of v1 and v2?

Re: assign observations into groups based on variables

PaigeMiller — Mon, 14 Feb 2022 22:28:06 GMT

Not sure exactly what you mean by "similar". Can you show us (a portion) of the clusters that result?

Re: assign observations into groups based on variables

leex1514 — Tue, 15 Feb 2022 02:52:37 GMT

For 3 groups i would like to assign, I hope to see each group's mean of N size and mean of score same (similar if not same): group 1 average N size =50 average score=60, group 2 average N size =50 average score=60,, group 3 average N size =50 average score=60 something like this. I am aware the averages can not be exactly same among groups but similar means within one or two points difference.

Re: assign observations into groups based on variables

leex1514 — Wed, 16 Feb 2022 13:05:58 GMT

Thank you RobPratt. This solution works as well.