BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ciro
Quartz | Level 8

Hi,

I am not sure whether this is the right forum. 

I have to assign to each object in dataset 'have1' a place of dataset 'have2' such that in the output dataset, the total number of place x group is equal to the variable 'n' in dataset 'have2'.

for each object the assignation should be random within the given constraint.

the output dataset should have the same number of observations of dataset 'have1' and be like:

object group place

172    IF        1

173    IF        5

........

 

any suggestion, and especially code, is greatly appreciated. 

Even more appreciated would be different options (OR, datasetp, IML...)

 

thank you very much in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

Here's one approach that uses the MILP solver in PROC OPTMODEL:

proc optmodel;
   /* declare parameters and read data */
   set OBJECTS;
   str group {OBJECTS};
   read data have1 into OBJECTS=[object] group;

   set <str,num> GROUP_PLACE;
   num n {GROUP_PLACE};
   read data have2 into GROUP_PLACE=[group place] n;

   set OBJECT_GROUP_PLACE = {o in OBJECTS, <g,p> in GROUP_PLACE: group[o] = g};

   /* X[o,g,p] = 1 if object o is assigned to group g and place p; 0 otherwise */
   var X {OBJECT_GROUP_PLACE} binary;

   /* assign each object to exactly one group-place */
   con AssignOnce {o in OBJECTS}:
      sum {<(o),g,p> in OBJECT_GROUP_PLACE} X[o,g,p] = 1;

   /* assign correct number of objects to each group-place */
   con Cardinality {<g,p> in GROUP_PLACE}:
      sum {<o,(g),(p)> in OBJECT_GROUP_PLACE} X[o,g,p] = n[g,p];

   /* call MILP solver with no objective */
   solve noobj;

   /* create output data set */
   create data want from [object group place]={<o,g,p> in OBJECT_GROUP_PLACE: X[o,g,p].sol > 0.5};
quit;

If you had some measure of desirability of placing object o in group g and place p, you could instead maximize the total desirability rather than assigning arbitrarily.

View solution in original post

2 REPLIES 2
RobPratt
SAS Super FREQ

Here's one approach that uses the MILP solver in PROC OPTMODEL:

proc optmodel;
   /* declare parameters and read data */
   set OBJECTS;
   str group {OBJECTS};
   read data have1 into OBJECTS=[object] group;

   set <str,num> GROUP_PLACE;
   num n {GROUP_PLACE};
   read data have2 into GROUP_PLACE=[group place] n;

   set OBJECT_GROUP_PLACE = {o in OBJECTS, <g,p> in GROUP_PLACE: group[o] = g};

   /* X[o,g,p] = 1 if object o is assigned to group g and place p; 0 otherwise */
   var X {OBJECT_GROUP_PLACE} binary;

   /* assign each object to exactly one group-place */
   con AssignOnce {o in OBJECTS}:
      sum {<(o),g,p> in OBJECT_GROUP_PLACE} X[o,g,p] = 1;

   /* assign correct number of objects to each group-place */
   con Cardinality {<g,p> in GROUP_PLACE}:
      sum {<o,(g),(p)> in OBJECT_GROUP_PLACE} X[o,g,p] = n[g,p];

   /* call MILP solver with no objective */
   solve noobj;

   /* create output data set */
   create data want from [object group place]={<o,g,p> in OBJECT_GROUP_PLACE: X[o,g,p].sol > 0.5};
quit;

If you had some measure of desirability of placing object o in group g and place p, you could instead maximize the total desirability rather than assigning arbitrarily.

FreelanceReinh
Jade | Level 19

Hi @ciro,

 

Here's an elementary approach:

/* Expand list of places */

data temp / view=temp;
call streaminit('MT64',27182818);
set have2;
do n=1 to n;
  r=rand('uniform');
  output;
end;
run;

/* Sort list of places randomly within groups */

proc sort data=temp out=places(keep=place);
by group r;
run;

/* Assign objects to groups (HAVE1 must be sorted by GROUP!) */

data want;
merge have1 places;
run;

/* Optional: Verify the constraint */

proc freq data=want noprint;
tables place*group / out=chk(drop=percent) sparse;
run;

proc compare data=have2(rename=(n=count)) c=chk method=absolute;
run;

Note that 20 of the 48 values of variable N in dataset HAVE2 are not exact integers (but close enough so that the DO loop in the first step works correctly).

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 533 views
  • 2 likes
  • 3 in conversation