SAS Optimization, and SAS Simulation Studio

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2016 11:24 AM

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!

Accepted Solutions

Solution

09-02-2016
12:39 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to triley

09-02-2016 12:23 PM

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 |

All Replies

Solution

09-02-2016
12:39 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to triley

09-02-2016 12:23 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RobPratt

09-02-2016 01:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to triley

09-02-2016 01:54 PM - edited 09-02-2016 02:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RobPratt

09-02-2016 02:06 PM

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

Thanks,

Tom