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!
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 |
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 |
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
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
Great, thank you very much. I will definitely give the book a good read.
Thanks,
Tom
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!
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.