proc optmodel - if then scenario

[triley]

Hi,

I had a problem that Rob Pratt helped me with a little while ago and I was trying to modify the problem slightly and haven't been able to figure out how...What I am trying to do is create 2 groups (ID) with equal amounts of volume (x1-x3) where you can think of x1-x3 as month1, month2, and month3 and the monthly sales volume within each variable. The 2 groups need to have close to the same volume for each month. This is the part that I have done already...

This is the part i'm stuck on...The ID's currently are part of 4 groups that i want to combine into 2. What I am trying to figure out how to do, is only change the group if it is in ID "1" or "3". The tricky part (at least what I believe is the tricky part) is I don't want to change the group if the ID is in "2" or "4", but I do want to use those ID's volume into the total for the new group.

So in the end there should be 2 groupID's (1 and 2) and groupID 1 should consist of all of the original ID 2's volume, plus the new volume that comes from ID's 1 and 3. GroupID 2 should consist of all original ID 4's volume, plus the new volume thta comes from ID's 1 and 3.

Here was the original set-up:

data have;
   input id $ curr_grp x1-x3;
   datalines;
a 1 10 15 8
b 1 11 14 9
c 1 12 16 10
d 1 9 16 8
e 2 8 12 10
f 2 8 10 11
g 2 11 14 12
h 2 13 10 9
i 3 8 16 15
j 3 10 11 8
k 3 11 15 14
l 3 16 13 10
m 4 10 15 14
n 4 12 8 14
o 4 7 12 13
p 4 9 14 10
;



%let num_vars   = 3;
%let num_groups = 2;

proc optmodel;
   set VARS   = 1..&num_vars;
   set GROUPS = 1..&num_groups;
   set  <STR> OBS;
   num a {OBS, VARS};
/*   num weight {j in VARS} = 1 / max {i in OBS} a[i,j];*/
read data have  into OBS=[id] {j in VARS} <a[id,j]=col('x'||j)> ;



   /* Assign[i,g] = 1 if observation i assigned to group g, 0 otherwise */
   var Assign {OBS, GROUPS} binary;

   con AssignOnce {i in OBS}:
      sum {g in GROUPS} Assign[i,g] = 1;

   con NearlyEqual {g in GROUPS}:
      floor(card(OBS)/&num_groups) <= sum {i in OBS} Assign[i,g] <= ceil(card(OBS)/&num_groups);

   impvar GroupSum {g in GROUPS, j in VARS} = sum {i in OBS} a[i,j] * Assign[i,g];

   var MinSum {VARS}, MaxSum {VARS};
   con MinSumCon {g in GROUPS, j in VARS}:
      MinSum[j] <= GroupSum[g,j];
   con MaxSumCon {g in GROUPS, j in VARS}:
      MaxSum[j] >= GroupSum[g,j];
   impvar Range {j in VARS} = MaxSum[j] - MinSum[j];
   min Objective = sum {j in VARS} Range[j];
/*    min Objective = sum {j in VARS} weight[j] * Range[j];*/
   solve;

   print Assign;
   print GroupSum;
   num groupID {OBS};
   for {i in OBS} do;
      for {g in GROUPS: Assign[i,g].sol > 0.5} do;
         groupID[i] = g;
         leave;
      end;
   end;
   create data want/*(drop=i)*/ from [i] {j in VARS} <col('x'||j)=a[i,j]> groupID;
quit;

Thanks in advance for the help!

[RobPratt · Accepted solution]

Your new constraint can be captured by fixing some Assign variables to 1.

First you need to declare and read the new curr_grp parameter:

   num curr_grp {OBS};
   read data have into OBS=[id] {j in VARS} <a[id,j]=col('x'||j)> curr_grp;

Then, before the SOLVE statement, you can fix the Assign variables as follows:

   for {i in OBS} do;
      if      curr_grp[i] = 2 then fix Assign[i,1] = 1;
      else if curr_grp[i] = 4 then fix Assign[i,2] = 1;
   end;

The resulting optimal solution does respect the fixings:

SAS Output

[1]	curr_grp	groupID
a	1	1
b	1	1
c	1	2
d	1	2
e	2	1
f	2	1
g	2	1
h	2	1
i	3	1
j	3	2
k	3	1
l	3	2
m	4	2
n	4	2
o	4	2
p	4	2

[triley]

Thank you for the quick response...It works perfectly!

I tried to do something similar to fix a particular ID but it didn't work and i'm not sure why. Sorry I am very new to optmodel and still learning some of the basics.

   for {j in VARS} do;
if id[j] = 'd' then fix Assign[i,2] = 1;
   end;

These are the errors I get:

ERROR 537-782: The symbol 'id' is unknown.
ERROR 631-782: The operand types for '=' are mismatched, found a number and a string.
ERROR 653-782: Subscript 1 must be a string, found a number.

Thanks,

Tom

[RobPratt]

Glad to help.

If you want to force every observation with id = 'd' to be assigned to group 2, you can use any one of the following equivalent loops:

   for {i in OBS} do;
      if i = 'd' then fix Assign[i,2] = 1;
   end;
   for {i in OBS: i = 'd'} do;
      fix Assign[i,2] = 1;
   end;
   for {i in OBS inter {'d'}} do;
      fix Assign[i,2] = 1;
   end;
   for {i in OBS inter {'d'}}
      fix Assign[i,2] = 1;

If you know that there is exactly one such observation, you can avoid the FOR loop:

   fix Assign['d',2] = 1;

A good way to learn PROC OPTMODEL is to study this book of 29 examples:

http://support.sas.com/documentation/cdl/en/ormpex/68157/HTML/default/viewer.htm#titlepage.htm

[triley]

Great, thank you very much. I will definitely give the book a good read.

Thanks,

Tom