Turn on suggestions

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

Showing results for

Options

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

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-11-2022 10:17 PM
(4878 views)

Hi. am trying to solve this problem below

I have a bunch of origins (O) , bunch of destinations (D) , bunch of shipments between them (i) . Have few box types to transport stuff for a given shipment from O to D. Each box has a cost and wt and vol restrictions. want the model to choose the optimum mix of box type and how many of them for each box type, based on cheapest cost. here is my attempted code. Tried and failed. Would appreciate if this can be mended to make it work. I need to find the optimum asset mix for a given shipment based on cheapest cost and also satisfying volume and weight capacities.

```
proc optmodel;
set <str> ORG;
read data CASUSER.Unique_ORG into ORG = [ORG];
set <str> DES;
read data CASUSER.Unique_DES into DES = [DES];
set <str> BOX;
read data CASUSER.Unique_BOX into BOX = [BOX];
set <str> ISN;
read data CASUSER.Unique_ISN into ISN= [ISN];
/* A Binary variable that is 0 or 1 for a given asset for a given lane, 0 indicated not available, 1- available */
var Is_BoxAvalable_for_a_lane {ORG,DES,Box} binary;
read data CASUSER.BOXSpecs into [ORG Des Box] Is_BoxAvalable_for_a_lane = BoxAvailability;
/*RATES*/
num PerBox_Based_Rate {ORG,DES,BOX} init 0;
read data CASUSER.BOXRATE (where= (RateBasis="PerBox" and Ratetype='Linehaul')) into [ORG DES BOX] PerBox_Based_Rate=Rate;
/* End of Rates */
/* Input Dims */
num Vol_Org_Des_ISN {ORG,DES,ISN} init 0;
read data CASUSER.InputData_AssetMix into [ORG DES ISN] Vol_Org_Des_ISN=Volume;
num Wt_Org_Des_ISN {ORG,DES,ISN} init 0;
read data CASUSER.InputData_AssetMix into [ORG DES ISN] Wt_Org_Des_ISN=Weight;
/* End of Input Dims */
*/ Start Vol and Wt Capacity for Boxes */
num Vol_Capacity {ORG,DES,BOX};
read data CASUSER.BOXSPECS into [ORG DES BOX] Vol_Capacity=Volume_Capacity;
print Vol_Capacity;
num Wt_Capacity {ORG,DES,BOX};
read data CASUSER.BOXSPECS into [ORG DES BOX] Wt_Capacity=Wt_Capacity;
*/ End of Vol and Wt Capacity for Boxes */
/* Decision Variable */
var BoxesNeeded {ORG,DES,ISN} >= 0;
/*Define Implicit Variables */
impvar PerBox_Based_Costs = sum {o in ORG, d in DES,b in BOX, i in ISN} PerBox_Based_Rate [o,d,b] * BoxesNeeded [o,d,i];
impvar Boxes{i in ISN}=sum{o in ORG, d in DES, b in BOX} BoxesNeeded [o,d,i];
Min TotalCost = PerBox_Based_Costs;
/* Constraints */
/*Make sure the Volume of a given ISN across boxes adds up the input */
con Check {i in ISN}:Boxes[i] = sum{o in ORG, d in DES, b in BOX} Is_BoxAvalable_for_a_lane[o,d,b]*Vol_Org_Des_ISN[o, d, i];
expand Check;
con Ensure_Unavailable_Asset_Not_Chosen {o in ORG, d in DES, i in ISN}:
sum {b in BOX} BoxesNeeded[o,d,i]<= sum {b in BOX} BoxesNeeded [o,d,i]*Is_BoxAvalable_for_a_lane[o,d,b];
expand EnSure_Unavailable_Asset_Not_Chosen;
con Vol_Constraint {i in ISN} :
sum {o in ORG, d in DES} Vol_Org_Des_ISN [o,d,i] <= sum {o in ORG, d in DES,b in BOX} Is_BoxAvalable_for_a_lane[o,d,b]*Vol_Capacity[o,d,b];
expand Vol_Constraint;
solve with milp;
```

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

The following code captures the business problem I think you want to solve, and I tried to keep things close to what you had already tried:

```
data Unique_ORG;
input ORG $;
datalines;
SHA
;
data Unique_DES;
input DES $;
datalines;
LAX
SEA
;
data Unique_BOX;
input BOX $;
datalines;
LCL
20F
40F
40H
45F
;
data InputData_AssetMix;
input ORG $ DES $ ISN $11. Volume Weight;
datalines;
SHA LAX SHA_LAX_123 32 15000
SHA LAX SHA_LAX_456 49 16000
SHA SEA SHA_SEA_789 2 4000
SHA SEA SHA_SEA_722 55 30000
;
data BOXSpecs;
input Org $ Des $ Box $ BoxAvailability Volume_Capacity Wt_Cap Volume_UOM $ Weight_UOM $;
datalines;
SHA LAX LCL 0 14 10000 CBM Kgs
SHA LAX 20F 1 30 14000 CBM Kgs
SHA LAX 40F 1 56 29000 CBM Kgs
SHA LAX 40H 1 65 35000 CBM Kgs
SHA LAX 45F 1 72 55000 CBM Kgs
SHA SEA LCL 1 17 10000 CBM Kgs
SHA SEA 20F 0 29 14000 CBM Kgs
SHA SEA 40F 1 60 29000 CBM Kgs
SHA SEA 40H 1 69 35000 CBM Kgs
SHA SEA 45F 1 75 55000 CBM Kgs
;
data BOXRATE;
input Org $ Des $ Box $ Ratetype $ Rate RateBasis $9.;
datalines;
SHA LAX LCL Linehaul 57.14285714 PerVolUOM
SHA LAX 20F Linehaul 800 PerBox
SHA LAX 40F Linehaul 1000 PerBox
SHA LAX 40H Linehaul 1100 PerBox
SHA LAX 45F Linehaul 1320 PerBox
SHA SEA LCL Linehaul 62.85714286 PerVolUOM
SHA SEA 20F Linehaul 880 PerBox
SHA SEA 40F Linehaul 1100 PerBox
SHA SEA 40H Linehaul 1210 PerBox
SHA SEA 45F Linehaul 1320 PerBox
;
proc optmodel;
set <str> ORG;
read data Unique_ORG into ORG=[ORG];
set <str> DES;
read data Unique_DES into DES=[DES];
set <str> BOX;
read data Unique_BOX into BOX=[BOX];
/* A Binary parameter that is 0 or 1 for a given asset for a given lane, 0 indicated not available, 1- available */
num Is_BoxAvalable_for_a_lane {ORG,DES,Box};
read data BOXSpecs into [ORG Des Box] Is_BoxAvalable_for_a_lane=BoxAvailability;
/* RATES */
num PerBox_Based_Rate {ORG,DES,BOX} init 0;
read data BOXRATE(where=(RateBasis="PerBox" and Ratetype='Linehaul')) into [ORG DES BOX] PerBox_Based_Rate=Rate;
print PerBox_Based_Rate;
/* End of Rates */
/* Input Dims */
set <str> ISN;
str Org_ISN {ISN};
str Des_ISN {ISN};
num Vol_ISN {ISN} init 0;
num Wt_ISN {ISN} init 0;
read data InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES Vol_ISN=Volume Wt_ISN=Weight;
/* End of Input Dims */
/* Start Vol and Wt Capacity for Boxes */
num Vol_Capacity {ORG,DES,BOX};
num Wt_Capacity {ORG,DES,BOX};
read data BOXSPECS into [ORG DES BOX] Vol_Capacity=Volume_Capacity Wt_Capacity=Wt_Cap;
/* End of Vol and Wt Capacity for Boxes */
/* Decision Variable */
var BoxesNeeded {ISN, BOX} >= 0 integer;
/* Define Implicit Variables */
impvar PerBox_Based_Costs = sum {i in ISN, b in BOX} PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
/* Objective */
Min TotalCost = PerBox_Based_Costs;
/* Constraints */
for {i in ISN, b in BOX: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b] = 0}
fix BoxesNeeded[i,b] = 0;
con Vol_Constraint {i in ISN}:
sum {b in BOX} Vol_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= Vol_ISN[i];
con Wt_Constraint {i in ISN}:
sum {b in BOX} Wt_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= Wt_ISN[i];
/* call MILP solver */
solve;
/* print optimal solution */
print BoxesNeeded;
quit;
```

Instead of declaring variables and then fixing some of them to 0, an alternative approach would be to use a sparse index set, say ISN_BOX, as illustrated in this Sparse Modeling example.

Note also that your problem completely decomposes by ISN into four independent problems. In such cases, you should consider using DECOMP, COFOR, or runOptmodel groupBy to exploit this structure.

18 REPLIES 18

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

A few questions and suggestions:

1. You declare Is_BoxAvalable_for_a_lane as a VAR but then read its values from a data table. Do you want the solver to determine the values, or should you instead declare this as a NUM?

2. You can simplify the code by combining READ DATA statements for the same table. For example, you can replace two READ DATA statements with this one:

```
read data CASUSER.InputData_AssetMix into [ORG DES ISN] Vol_Org_Des_ISN=Volume Wt_Org_Des_ISN=Weight;
```

3. You declare PerBox_Based_Costs as an IMPVAR and then use it as the objective. A simpler approach is:

```
Min PerBox_Based_Costs = sum {o in ORG, d in DES,b in BOX, i in ISN} PerBox_Based_Rate [o,d,b] * BoxesNeeded [o,d,i];
```

4. You declare BoxesNeeded as a continuous variable, but should this instead be an integer variable?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Rob

Thank you. You were right. I have fixed the issues

1) I have this now num Is_BoxAvalable_for_a_lane {ORG,DES,Box}; This is a binary variable that I get it from the user as input. I do not want model to calculate this for me now.

2) have combined them into once as u said. thank you

3) noted. yeah right now i have only one cost and so i have just that cost in my objective function. but i may have more going forward . so thought of separating it out. Nevertheless, I see your approach and will use it later

4) You are right. I have changed to this as per your suggetsion:

var BoxesNeeded {ORG,DES,ISN} integer;

I am feeling rusty as it has been a long time since i did any coding of any sort.. Can you guide me in the following constraints by making sure the model choses the lowest cost mix of box types and numbers.

a ) A given shipments' box type and number determination by model does not exceed boxes' wt and vol capacity

b) Making sure the sum of volume in all the boxes in all shipments is equal to the volume in the data.

c) BigM. I guess one for wt and volume

d) Making sure a box that is unavailable for a given lane is not chosen.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Can you please provide sample data sets?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob

Here are sample data sets: These are for the protptype model.

**ORG:** {SHA} -- these are list of origins - Have only 1 so far but will be more in full model.

**DES:** {SEA, LAX} -- these are list of destinations.

**ISN:** These are shipments. ISN is the shipment ID. Want to optimize the asset mix for this given ISN.

ISN | Volume | Weight |

SHA_LAX_123 | 32 | 15000 |

SHA_LAX_456 | 49 | 16000 |

SHA_SEA_789 | 2 | 4000 |

SHA_SEA_722 | 55 | 30000 |

**Box Specs:**

Org | Des | Box | BoxAvailability | Volume_Capacity | Wt_Cap | Volume_UOM | Weight_UOM |

SHA | LAX | LCL | 0 | 14 | 10000 | CBM | Kgs |

SHA | LAX | 20F | 1 | 30 | 14000 | CBM | Kgs |

SHA | LAX | 40F | 1 | 56 | 29000 | CBM | Kgs |

SHA | LAX | 40H | 1 | 65 | 35000 | CBM | Kgs |

SHA | LAX | 45F | 1 | 72 | 55000 | CBM | Kgs |

SHA | SEA | LCL | 1 | 17 | 10000 | CBM | Kgs |

SHA | SEA | 20F | 0 | 29 | 14000 | CBM | Kgs |

SHA | SEA | 40F | 1 | 60 | 29000 | CBM | Kgs |

SHA | SEA | 40H | 1 | 69 | 35000 | CBM | Kgs |

SHA | SEA | 45F | 1 | 75 | 55000 | CBM | Kgs |

Box Rates: Right now, I am using only PerBox rates only. But will use PerVolUOM based as well.

Org | Des | Box | Ratetype | Rate | RateBasis |

SHA | LAX | LCL | Linehaul | 57.14285714 | PerVolUOM |

SHA | LAX | 20F | Linehaul | 800 | PerBox |

SHA | LAX | 40F | Linehaul | 1000 | PerBox |

SHA | LAX | 40H | Linehaul | 1100 | PerBox |

SHA | LAX | 45F | Linehaul | 1320 | PerBox |

SHA | SEA | LCL | Linehaul | 62.85714286 | PerVolUOM |

SHA | SEA | 20F | Linehaul | 880 | PerBox |

SHA | SEA | 40F | Linehaul | 1100 | PerBox |

SHA | SEA | 40H | Linehaul | 1210 | PerBox |

SHA | SEA | 45F | Linehaul | 1320 | PerBox |

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob

For constraint (a) i did this but i know it is not right. For a given Shipment, I want to make sure that what the model says in terms of box mix, that total available capacity is greater than the Volume of the shipment. But I know that what i have below is not right because it is summing up which I know it should not. I tried few things but not able to get it right.

```
con Check {o in ORG, d in DES, i in ISN}:
sum {b in BOX} (BoxesNeeded[o,d,i]*Is_BoxAvalable_for_a_lane[o,d,b]*Vol_Capacity[o,d,b]) >=
Vol_Org_Des_ISN[o,d,i];
```

Also I changed my impvar to this but not yet used it as of now.

`impvar Boxes{i in ISN}=sum{o in ORG, d in DES, b in BOX} BoxesNeeded [o,d,i];`

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

The following code captures the business problem I think you want to solve, and I tried to keep things close to what you had already tried:

```
data Unique_ORG;
input ORG $;
datalines;
SHA
;
data Unique_DES;
input DES $;
datalines;
LAX
SEA
;
data Unique_BOX;
input BOX $;
datalines;
LCL
20F
40F
40H
45F
;
data InputData_AssetMix;
input ORG $ DES $ ISN $11. Volume Weight;
datalines;
SHA LAX SHA_LAX_123 32 15000
SHA LAX SHA_LAX_456 49 16000
SHA SEA SHA_SEA_789 2 4000
SHA SEA SHA_SEA_722 55 30000
;
data BOXSpecs;
input Org $ Des $ Box $ BoxAvailability Volume_Capacity Wt_Cap Volume_UOM $ Weight_UOM $;
datalines;
SHA LAX LCL 0 14 10000 CBM Kgs
SHA LAX 20F 1 30 14000 CBM Kgs
SHA LAX 40F 1 56 29000 CBM Kgs
SHA LAX 40H 1 65 35000 CBM Kgs
SHA LAX 45F 1 72 55000 CBM Kgs
SHA SEA LCL 1 17 10000 CBM Kgs
SHA SEA 20F 0 29 14000 CBM Kgs
SHA SEA 40F 1 60 29000 CBM Kgs
SHA SEA 40H 1 69 35000 CBM Kgs
SHA SEA 45F 1 75 55000 CBM Kgs
;
data BOXRATE;
input Org $ Des $ Box $ Ratetype $ Rate RateBasis $9.;
datalines;
SHA LAX LCL Linehaul 57.14285714 PerVolUOM
SHA LAX 20F Linehaul 800 PerBox
SHA LAX 40F Linehaul 1000 PerBox
SHA LAX 40H Linehaul 1100 PerBox
SHA LAX 45F Linehaul 1320 PerBox
SHA SEA LCL Linehaul 62.85714286 PerVolUOM
SHA SEA 20F Linehaul 880 PerBox
SHA SEA 40F Linehaul 1100 PerBox
SHA SEA 40H Linehaul 1210 PerBox
SHA SEA 45F Linehaul 1320 PerBox
;
proc optmodel;
set <str> ORG;
read data Unique_ORG into ORG=[ORG];
set <str> DES;
read data Unique_DES into DES=[DES];
set <str> BOX;
read data Unique_BOX into BOX=[BOX];
/* A Binary parameter that is 0 or 1 for a given asset for a given lane, 0 indicated not available, 1- available */
num Is_BoxAvalable_for_a_lane {ORG,DES,Box};
read data BOXSpecs into [ORG Des Box] Is_BoxAvalable_for_a_lane=BoxAvailability;
/* RATES */
num PerBox_Based_Rate {ORG,DES,BOX} init 0;
read data BOXRATE(where=(RateBasis="PerBox" and Ratetype='Linehaul')) into [ORG DES BOX] PerBox_Based_Rate=Rate;
print PerBox_Based_Rate;
/* End of Rates */
/* Input Dims */
set <str> ISN;
str Org_ISN {ISN};
str Des_ISN {ISN};
num Vol_ISN {ISN} init 0;
num Wt_ISN {ISN} init 0;
read data InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES Vol_ISN=Volume Wt_ISN=Weight;
/* End of Input Dims */
/* Start Vol and Wt Capacity for Boxes */
num Vol_Capacity {ORG,DES,BOX};
num Wt_Capacity {ORG,DES,BOX};
read data BOXSPECS into [ORG DES BOX] Vol_Capacity=Volume_Capacity Wt_Capacity=Wt_Cap;
/* End of Vol and Wt Capacity for Boxes */
/* Decision Variable */
var BoxesNeeded {ISN, BOX} >= 0 integer;
/* Define Implicit Variables */
impvar PerBox_Based_Costs = sum {i in ISN, b in BOX} PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
/* Objective */
Min TotalCost = PerBox_Based_Costs;
/* Constraints */
for {i in ISN, b in BOX: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b] = 0}
fix BoxesNeeded[i,b] = 0;
con Vol_Constraint {i in ISN}:
sum {b in BOX} Vol_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= Vol_ISN[i];
con Wt_Constraint {i in ISN}:
sum {b in BOX} Wt_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= Wt_ISN[i];
/* call MILP solver */
solve;
/* print optimal solution */
print BoxesNeeded;
quit;
```

Instead of declaring variables and then fixing some of them to 0, an alternative approach would be to use a sparse index set, say ISN_BOX, as illustrated in this Sparse Modeling example.

Note also that your problem completely decomposes by ISN into four independent problems. In such cases, you should consider using DECOMP, COFOR, or runOptmodel groupBy to exploit this structure.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob. Thanks a lot. Let me try this code and let you know.

Also, I am open to try any new thing that you recommend like DECOMP etc. Let me try to get the code that you have closest to what I tried and take it from there. Will keep you posted soon

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob.

I was able to run the code that you had provided. This is very great progress. I did not know how to write the for loop code that you have mentioned. Here is my data set (attached) that I used in excel format. The input data for shipments in slightly bigger. It has 25 line items compared to 4 before. Apart from that I added two columns in BoxSpecs . But i have not yet used them in the code yet. Will use later. This is not going to affect anything now.

My observations

(a) For SHA_LAX, the box availability is 0. So I want to make sure that SHA-LAX does not use LCL. I thought fixing that binary variable using the for loop does that. May be I am missing something here.

(b) The solver suggested this, (same as what you recommended): The problem has a decomposable structure with 12 blocks. The largest block covers 8.33% of the constraints in the problem. The DECOMP option with METHOD=CONCOMP is recommended for solving problems with this structure. I would like to use this feature . I saw this.But I am not sure how to define the data for this. Can you help me with that?

(c) About other constraints, am i missing BigM constraint or anything else?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Rob.

I had uploaded the wrong data set and so it was messed up. Please ignore my earlier attached data sets. Here is the correct data set attached for your reference just in case. Now, I reran the model with the correct data set. Model seems to be behaving right. I shall do the create data to spit the outputs and analyze. I will try to test for more scenarios. So for now, the questions that I have are

(a) The solver suggested this, (same as what you recommended): The problem has a decomposable structure with 12 blocks. The largest block covers 8.33% of the constraints in the problem. The DECOMP option with METHOD=CONCOMP is recommended for solving problems with this structure. I would like to use this feature . I saw this.But I am not sure how to define the data for this. Can you help me with that?

(b) About other constraints, am i missing BigM constraint or anything else?

thanks a lot

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

To follow the log NOTE about using DECOMP, you need only change the SOLVE statement to this:

```
solve with milp / decomp=(method=concomp);
```

Regarding whether you are missing any constraints, the code I supplied captures everything you described, as far as I understand. I am glad to hear that it is behaving correctly for you. Optimization modeling is often an iterative process where you solve an initial model, examine the results, realize that you forgot about some restrictions, modify the model, and repeat. Running different scenarios is a good way to validate whether the optimization model does what you want.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

HI rob

Thanks a lot. Let me try that. I am going to try different combo to make sure its behaving.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob

I tried the SPARSE modeling that you recommended. Let me explain the reason behind it as well.

Org | Des | Box | Ratetype | Rate | RateBasis |

SHA | SEA | LCL | Linehaul | 62.85714286 | PerVolUOM |

SHA | SEA | 20F | Linehaul | 880 | PerBox |

SHA | SEA | 40F | Linehaul | 1100 | PerBox |

SHA | SEA | 40H | Linehaul | 1210 | PerBox |

SHA | SEA | 45F | Linehaul | 1320 | PerBox |

If you look at the table above, SHA-SEA, there is no rate for LCL in "PerBOX" Ratetype. It has only PerVolUOM. The model has initialized the rate to be 0 as in here for all PerBox_Based_Rate combos.

```
num PerBox_Based_Rate {ORG,DES,BOX} init 0;
read data CASUSER.BOXRATE (where= (RateBasis="PerBox" and Ratetype='Linehaul')) into [ORG DES BOX] PerBox_Based_Rate=Rate;
```

Since the rate is 0 for LCL for SHA-SEA and the LCL's availability is 1 (which is fine), the model automatically picks this for all SHA-SEA shipments. In order to fix this, I did this below.

```
set PerBox_Based_Rate_NoZeroes = {o in ORG, d in DES, b in BOX: PerBox_Based_Rate[o,d,b] > 0};
impvar PerBox_Based_Costs= sum {<o,d,b> in PerBox_Based_Rate_NoZeroes, i in ISN}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
```

The idea is that the model uses only those non zero values in the calculations. But when I expanded the model, since i am summing it up for a given ISN, the rates are also summed up which I don't want. I tried different things but could not get it done. Am i doing the right approach? if yes, can u tell me the syntax for Impvar PerBox_Based_Costs . This is a cost that should be calculate only for those where the rate is not 0.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Here is an approach that is close to what you tried:

```
set PerBox_Based_Rate_NoZeroes = {o in ORG, d in DES, b in BOX: PerBox_Based_Rate[o,d,b] > 0};
impvar PerBox_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
```

An alternative approach is to avoid reading the unwanted observations in the first place:

```
set <str,str,str> PerBox_Based_Rate_NoZeroes;
num PerBox_Based_Rate {PerBox_Based_Rate_NoZeroes};
read data BOXRATE(where=(RateBasis="PerBox" and Ratetype='Linehaul' and Rate>0)) into PerBox_Based_Rate_NoZeroes=[ORG DES BOX] PerBox_Based_Rate=Rate;
print PerBox_Based_Rate;
```

impvar PerBox_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob

I tried that but somehow I am not able to see the expected output. I have attached the dataset I used .

I am looking for a ISN='SHA_SEA_722' that has 55 Vol and 30000 Wt. For SHA-SEA the LCL availability is there. However, the rate for LCL for SHA-SEA is not a PerBox Based one. It is a Per Vol based one that i have not even taken into the model yet. So, I would expect that the model does not pick LCL as the winner for SHA-SEA because there is no BoxBased Rate. We have culled out only those rates where Rate >0. But I am not sure how does the model pick LCL for SHA-SEA. We have not even initialized the costs to 0. Here is my code below. Once I am done with this , I want to add couple of more constraints, that are like if a particular Box is selected for a given ISN, the volume is atleast 'x". I will let you know on that if i face issues but i want to understand / solve this LCL thing first.

```
proc optmodel;
set <str> ORG;
read data CASUSER.Unique_ORG into ORG = [ORG];
set <str> DES;
read data CASUSER.Unique_DES into DES = [DES];
set <str> BOX;
read data CASUSER.Unique_BOX into BOX = [BOX];
/*Read ISN Wt and Volume for a given ISN* - START */
set <str> ISN;
str Org_ISN {ISN};
str Des_ISN {ISN};
num Vol_ISN {ISN} init 0;
num Wt_ISN {ISN} init 0;
read data CASUSER.InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES Vol_ISN=Volume Wt_ISN=Weight;
/*Read ISN Wt and Volume for a given ISN* - END*/
/*Read Wt and Volume Capacity for a given ORG, DES, BOX - START */
num Volume_Capacity {ORG,DES,BOX} init 0; num Volume_Min {ORG,DES,BOX} init 0;
num Wt_Capacity {ORG,DES,BOX} init 0;
read data CASUSER.BOXSPECS into [ORG DES BOX]
Volume_Capacity=Volume_Capacity
Wt_Capacity=Weight_Capacity
Volume_Min=Volume_Min;
/*Read Wt and Volume Capacity for a given ORG, DES, BOX - END */
/* A Binary variable that is 0 or 1 for a given asset for a given lane, 0 indicated not available, 1- available */
num Is_BoxAvalable_for_a_lane {ORG,DES,Box};
read data CASUSER.BOXSpecs into [ORG Des Box] Is_BoxAvalable_for_a_lane = BoxAvailability;
/* Decision Variable - START */
var BoxesNeeded {ISN,BOX}>=0 integer;
/* Decision Variable - END */
/*Define Rates and Implicit Variables - START */
set <str,str,str> PerBox_Based_Rate_NoZeroes;
num PerBox_Based_Rate {PerBox_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE
(where=(RateBasis="PerBox" and Ratetype='Linehaul' and Rate>0))
into PerBox_Based_Rate_NoZeroes=[ORG DES BOX] PerBox_Based_Rate=Rate;
impvar PerBox_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
/*Define Implicit Variables - END */
print PerBox_Based_Rate;
Min TotalCost = PerBox_Based_Costs;
/* Constraints - START */
for {i in ISN, b in BOX: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b] = 0}
fix BoxesNeeded[i,b] = 0;
con Vol_Constraint {i in ISN}:
sum {b in BOX} Volume_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= Vol_ISN[i];
con Wt_Constraint {i in ISN}:
sum {b in BOX} Wt_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= Wt_ISN[i];
/* Constraints - END */
solve with milp / decomp=(method=concomp);
print BoxesNeeded;
expand;
```

Here are my results of expand: I just focussed on this one ISN for test purposes.

Var BoxesNeeded[SHA_SEA_722,'40F'] INTEGER >= 0 Fix BoxesNeeded[SHA_SEA_722,'20F'] BINARY = 0 Var BoxesNeeded[SHA_SEA_722,'40H'] INTEGER >= 0 Var BoxesNeeded[SHA_SEA_722,'45F'] INTEGER >= 0 Var BoxesNeeded[SHA_SEA_722,LCL] INTEGER >= 0 Impvar PerBox_Based_Costs = 880*BoxesNeeded[SHA_SEA_722,'20F'] + 1100*BoxesNeeded[SHA_SEA_722,'40F'] + 1210* BoxesNeeded[SHA_SEA_722,'40H'] + 1320*BoxesNeeded[SHA_SEA_722,'45F'] Minimize TotalCost=PerBox_Based_Costs Constraint Vol_Constraint[SHA_SEA_722]: 60*BoxesNeeded[SHA_SEA_722,'40F'] + 29*BoxesNeeded[SHA_SEA_722,'20F'] + 69* BoxesNeeded[SHA_SEA_722,'40H'] + 75*BoxesNeeded[SHA_SEA_722,'45F'] + 17*BoxesNeeded[SHA_SEA_722,LCL] >= 55 Constraint Wt_Constraint[SHA_SEA_722]: 29000*BoxesNeeded[SHA_SEA_722,'40F'] + 14000*BoxesNeeded[SHA_SEA_722,'20F'] + 35000* BoxesNeeded[SHA_SEA_722,'40H'] + 55000*BoxesNeeded[SHA_SEA_722,'45F'] + 10000*BoxesNeeded[SHA_SEA_722,LCL] >= 30000

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.