BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
triley
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

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

View solution in original post

4 REPLIES 4
RobPratt
SAS Super FREQ

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
Obsidian | Level 7

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
SAS Super FREQ

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
Obsidian | Level 7

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

 

Thanks,

Tom

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 4 replies
  • 2033 views
  • 1 like
  • 2 in conversation