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 07-20-2023 06:00 AM
(933 views)

Rob

Thanks for your support as always. I am learning a lot of stuff thanks to you.

I am creating a new thread, based on this, (earlier thread), just to be clear. I think we are almost done, just left with **4** questions/help. I am trying to replicate the toy model to the full model by adding indices for T1 (days, T2(weeks) and Org, Des because rates and specs are function of org, des, box, mol, t1 and t2 as you know. Based on your earlier comment, I realized that we should go for the case where "vendor cooperates with each other to reduce overall network cost", in which case we can make the model simpler by not having the fix/unfix and the second solve. The $3,622 is what we need as it is the true optimum. When I remove those fix/unfixes , rational constraint and second solve, the model gives $3,622 which is what we want. I want the same $3,622 when I do the full model. That is my goal. Now I have 4 questions when we translate this to full model . My full model code is at the bottom of these 4 questions.

(1) **Without i in ISN:** For the full model, I have no problems when I have i in ISN because it has Org, DES, T1 and T2 in it and I can cull out using [i], example Org[i]. But when it comes to CFS constraints, impvars, I am not able to do it correctly as there are no i in ISN.

**(2) Proportion:** There is no proportion decision variable and a constraint related to it in the code. But I tested few different numbers of Volume in ISNs and the code seems to be doing it correctly. I just wanted to make sure that the proportion is accounted for, i.e., for a given ISN, the proportion in each box adds up to 1 for CYS. And for CFS, for a given week, the proportion of Total Volume adds up to 1. The following code is what we had earlier where there was no MOL, day and week, that was done a year ago. I tried in the full model but was not successful. If we do not need these proportion variables at all, it then it is fine.

```
var Proportion {ISN,BOX} >= 0 <= 1; impvar VolumeInsideBox {i in ISN, b in BOX} = Volume_ISN[i] * Proportion[i,b];
con Volume_Constraint {i in ISN, b in BOX}: Volume_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b] >= VolumeInsideBox[i,b];
```

**3) Create Data step: **For the output, I would like to have it in a format like the one that I have attached in this spreadsheet (adding on to the Toy.xlsx) from cells A25 to X29. Again, I tried (just for CYS) in the full model but was facing syntax issues.

(4)** Rational Vendor error: **This is the last preference as we are not using the constraint anymore, now that we said we will let the vendor cooperate. This will be just for my understanding. Not an urgent one.

```
126 con RationalVendor {i in ISN}:
127 IsMol[i,'CFS'] = 1 implies
-------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, .., /, <>, ><, BY, CROSS, DIFF, ELSE, INTER,
SUFFIXES, SYMDIFF, TO, UNION, ^, ||.
ERROR 76-322: Syntax error, statement will be ignored.
128 vol_ISN[i] * (BoxBasedCostCFS + sum {j in ISN} (volRate['CFS'] * vol_ISN[j] + shipmentRate['CFS']) * IsMol[j,'CFS'])
129 <= optCYSCost[i] * TotalVol['CFS'];
```

My full code is here below.

```
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> MOL;
read data CASUSER.Unique_MOL into MOL = [MOL];
set <str> T1;
read data CASUSER.Unique_T1 into T1 = [T1];
set <str> T2;
read data CASUSER.Unique_T2 into T2 = [T2];
/*Read ISN Wt and Volume for a given ISN* - START */
set <str> ISN;
str Org_ISN {ISN};
str Des_ISN {ISN};
str T1_ISN {ISN};
str T2_ISN {ISN};
num Volume_ISN {ISN} init 0;
num Weight_ISN {ISN} init 0;
read data CASUSER.InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES Volume_ISN=Volume Weight_ISN=Weight T1_ISN=T1 T2_ISN=T2;
num T2_Volume{T2} init 0;
read data CASUSER.Unique_T2 into [T2] T2_Volume=T2_Volume;
num T2_Weight{T2} init 0;
read data CASUSER.Unique_T2 into [T2] T2_Weight=T2_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,MOL,T1,T2} init 0; num Volume_Min {ORG,DES,BOX,MOL,T1,T2} init 0;
num Weight_Capacity {ORG,DES,BOX,MOL,T1,T2} init 0; num Weight_Min {ORG,DES,BOX,MOL,T1,T2} init 0;
read data CASUSER.BOXSPECS into [ORG DES BOX MOL T1 T2]
Volume_Capacity=Volume_Capacity
Weight_Capacity=Weight_Capacity
Volume_Min=Volume_Min
Weight_Min=Weight_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,MOL,T1,T2};
read data CASUSER.BOXSpecs into [ORG Des Box Mol T1 T2] Is_BoxAvalable_for_a_lane = BoxAvailability;
/* Decision Variable - START */
var BoxesNeeded_CYS {ISN,BOX,T1,T2} >= 0 integer;
var BoxesNeeded_CFS {BOX,T2} >= 0 integer;
var Is_ISN_MOL{ISN,MOL} binary;
/* Decision Variable and Associated Impvars - END */
/*Define Rates and ImplicitVariables START */
/*Box Based Costs START */
set <str,str,str,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 MOL T1 T2] PerBox_Based_Rate=Rate;
impvar PerBox_Based_Costs_CYS {i in ISN} = sum {b in BOX,m in {'CYS'},d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded_CYS[i,b,d,w];
impvar PerBox_Based_Costs_CFS {w in T2} = sum {b in BOX} PerBox_Based_Rate[b,w] * BoxesNeeded_CFS[b,w];
impvar TotalBoxBasedCost = sum {i in ISN} PerBox_Based_Costs_CYS[i] + sum {w in T2} PerBox_Based_Costs_CFS[w];
/*Box Based Costs END */
/*Vol Based Costs START*/
set <str,str,str,str,str,str> PerVol_Based_Rate_NoZeroes;
num PerVol_Based_Rate {PerVol_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE (where=(RateBasis="PerVolUOM" and Ratetype='Carrier_BOL' and Rate>0))
into PerVol_Based_Rate_NoZeroes=[ORG DES MOL BOX T1 T2] PerVol_Based_Rate=Rate;
impvar VolBasedCost {i in ISN} = sum {b in BOX,m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]> in PerVol_Based_Rate_NoZeroes}
Volume_ISN[i] * PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * Is_ISN_Mol[i,m];
impvar TotalVolBasedCost = sum {i in ISN} VolBasedCost[i];
/*Vol Based Costs END*/
/*Shipment Based Costs START*/
set <str,str,str,str,str,str> PerShp_Based_Rate_NoZeroes;
num PerShp_Based_Rate {PerShp_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE (where=(RateBasis="PerShipment" and Ratetype='Export_Declaration' and Rate>0))
into PerShp_Based_Rate_NoZeroes=[ORG DES MOL BOX T1 T2] PerShp_Based_Rate=Rate;
impvar ShipmentBasedCost {i in ISN} = sum {b in BOX,m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]> in PerShp_Based_Rate_NoZeroes}
PerShp_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * Is_ISN_Mol[i,m];
impvar TotalShipmentBasedCost = sum {i in ISN} ShipmentBasedCost[i];
/*Shipment Based Costs END*/
/*Define Rates and ImplicitVariables END*/
Min TotalCost = TotalBoxBasedCost + TotalVolBasedCost + TotalShipmentBasedCost;
/* Constraints START*/
for {i in ISN, b in BOX,m in MOL,d in T1,w in T2: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b,m,d,w] = 0}
fix BoxesNeeded_CYS[i,b,d,w] = 0;
for {i in ISN, b in BOX,m in MOL,d in T1,w in T2: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b,m,d,w] = 0}
fix BoxesNeeded_CFS[b,w] = 0;
con OneMOL{i in ISN}:
sum{M in MOL} Is_ISN_MOL[i,m] = 1; /* This is to make sure one ISN can go either CYS or CFS */
con BoxConCYS {i in ISN}:
sum {b in BOX} Volume_Capacity[Org_ISN[i],Des_ISN[i],b,'CYS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,T1_ISN[i],T2_ISN[i]] >= Volume_ISN[i] * Is_ISN_Mol[i,'CYS'];
impvar TotalVol {m in MOL} = sum {i in ISN} Volume_ISN[i] * Is_ISN_Mol[i,m];
con BoxConCFS: {i in ISN}:
sum {b in BOX} Volume_Capacity[Org_ISN[i],Des_ISN[i],b,'CYS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,T2_ISN[i]] >= TotalVol['CFS'];
solve with milp / decomp=(method=concomp);
num optCYSCost {ISN};
for {i in ISN} optCYSCost[i] = PerBox_Based_Costs_CYS[i] + VolBasedCost[i] + ShipmentBasedCost[i];
print BoxBasedCostCYS VolBasedCost ShipmentBasedCost optCYSCost;
num volShareCFS {i in ISN} = vol_ISN[i] * Is_ISN_Mol[i,'CFS'].sol / TotalVol['CFS'].sol;
print IsMol;
print NumBoxesCYS NumBoxesCFS;
print BoxBasedCostCYS BoxBasedCostCFS;
print volShareCFS VolBasedCost TotalVolBasedCost TotalVol;
print ShipmentBasedCost;
quit;
```

1 ACCEPTED SOLUTION

Accepted Solutions

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

Except for BoxRate.xlsx, it looks like you didn't provide the latest data tables you are using.

To avoid the duplicate key warnings, make sure the columns that appear within the square brackets [] in the READ DATA statement uniquely identify the observation.

The latest code you sent yields errors related to two IMPVAR declarations. Here are two corrections that should help you get further:

```
impvar VolBasedCost {i in ISN} = Volume_ISN[i] *
/* sum {m in MOL} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]]*Is_ISN_MOL[i,m];*/
sum {<Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]> in PerVol_Based_Rate_NoZeroes} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m];
```

```
impvar ShipmentBasedCost {i in ISN} =
/* sum {m in MOL} PerShp_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m];*/
sum {<Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]> in PerShp_Based_Rate_NoZeroes} PerShp_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m];
```

12 REPLIES 12

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

1. Please see my response here: https://communities.sas.com/t5/Mathematical-Optimization/AssetOptimization-Contd/m-p/885516/highligh...

2. Do you still need to account for capacity and minThreshold for both volume and weight? Your latest code uses only volume capacity.

3. If the solution uses more than one box type per shipment, do you want multiple rows for that shipment?

4. That syntax error about IMPLIES means that you are not running a version that supports indicator constraints, which are available only in SAS Viya 4.

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

Rob

First of all, I can't thank you enough for your support and patience on this one. I tried lot of things to convert to full model. The model is picking up only CFS for a total cost of $692 (173 * $4). So I am not writing something correct. My code is below. Requesting you to see where i am missing and what needs to be done to get to the optimum $3,622. The Box based costs are not getting calculated correctly for some reason that I am not able to figure it out. I am right now feeling low on confidence for I could not convert it successfully and not getting the syntaxes right and going in circles sometimes. If this is fixed, I will expand the model for many weeks and many vendors , which I have a Monday deadline.

- In this model, please ignore weight, though there are weight variables. . I can add weight later on. Only focus is on "Volume".
- Please ignore minimum thresholds , that can be added later on. Only focus is on Volume Capacity as constraint. So far this is what the model does and you have correctly used in your code as well. So far so good.
- The syntax of IMPLIES - I got it. not a worry for me right now as we are now in the "all vendors cooperate" mode.
- I did use the response in this one to expand to the full model. I had to add variables like "PerBox_Based_Rate_CFS" and var Is_ISN_MOL_T2{ISN,MOL,T2} binary;
- In the toy model, you had the below table for VolRate and ShipmentRates.

```
num volRate {MOL} = [0 4];
num shipmentRate {MOL} = [50 0];
```

But in the full model, when I use the read data only CFS Mol has Volbased rates and only CYS Mol has shipment based rates. When I ran just like, that it threw an error saying "for vol rates, no CYS combo was found" and so I manually added "0" for CYS Volrates and "0" for CFS ShipmentBased rates. I shall attach this new file in the next comment. When I add this it is ok For now this is fine and I can go with this approach if u say ok. I would like to know if there is an efficient way to do this without adding "0" rates artificially for some rateytpes. For PerBox_Based_Rate, we had this code below, but that did not pose any problem because all combinations of ORG DES BOX MOL T1 and T2 was there. But if it is not there , will there be a problem?

set <str,str,str,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 MOL T1 T2] PerBox_Based_Rate=Rate;df

6) Duplicates : if u look below,

```
num Volume_Capacity {BOX};
223 read data CASUSER.BOXSPECS into [BOX] Volume_Capacity=Volume_Capacity;
WARNING: Duplicate key <'20F'> was read at observation 4.I
```

i have given one instance of duplicates but there are many. The volume capacity is indexed over BOX. But I am extracting from BOXSPECS which has combinations of MOL, T1 and T2. Ideally, the rates and specs were prepared for all combinations of ORG, DES, BOX, MOL, T1, T2 so that we can have different rates wherever applicable. Even for the same lane, SHA-ATL the 20F rate may be $800 in Week 1 and could be $850 the next week just as an example. So , want to learn the efficient way to handling this "PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]]".

7. About the create data output. yes, if the solution uses more than one box type per shipment, I would like to have multiple rows for that shipment.

😎My code below:

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> MOL; read data CASUSER.Unique_MOL into MOL = [MOL]; set <str> T1; read data CASUSER.Unique_T1 into T1 = [T1]; set <str> T2; read data CASUSER.Unique_T2 into T2 = [T2]; /*Read ISN Wt and Volume for a given ISN* - START */ set <str> ISN; str Org_ISN {ISN}; str Des_ISN {ISN}; str T1_ISN {ISN}; str T2_ISN {ISN}; num Volume_ISN {ISN}; num Weight_ISN {ISN}; read data CASUSER.InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES Volume_ISN=Volume Weight_ISN=Weight T1_ISN=T1 T2_ISN=T2; num T2_Volume{T2} init 0; read data CASUSER.Unique_T2 into [T2] T2_Volume=T2_Volume; num T2_Weight{T2} init 0; read data CASUSER.Unique_T2 into [T2] T2_Weight=T2_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_Min {ORG,DES,BOX,MOL,T1,T2} init 0; num Weight_Capacity {ORG,DES,BOX,MOL,T1,T2} init 0; num Weight_Min {ORG,DES,BOX,MOL,T1,T2} init 0; read data CASUSER.BOXSPECS into [ORG DES BOX MOL T1 T2] Weight_Capacity=Weight_Capacity Volume_Min=Volume_Min Weight_Min=Weight_Min; num Volume_Capacity {BOX}; read data CASUSER.BOXSPECS into [BOX] Volume_Capacity=Volume_Capacity; num Volume_Capacity_CFS{BOX,T2}; read data CASUSER.BOXSPECS into [BOX T2] Volume_Capacity_CFS=Volume_Capacity; /*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,MOL,T1,T2}; read data CASUSER.BOXSpecs into [ORG Des Box Mol T1 T2] Is_BoxAvalable_for_a_lane = BoxAvailability; /* Decision Variable - START */ var BoxesNeeded_CYS {i in ISN,b in BOX} >= 0 integer <= ceil(Volume_ISN[i] / Volume_Capacity[b]);; var BoxesNeeded_CFS {b in BOX,w in T2} integer >=0 <= ceil(sum {i in ISN} Volume_ISN[i] / Volume_Capacity[b]); var Is_ISN_MOL{ISN,MOL} binary; var Is_ISN_MOL_T2{ISN,MOL,T2} binary; /* Decision Variable and Associated Impvars - END */ /*Define Rates and ImplicitVariables START */ /*Box Based Costs START */ set <str,str,str,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 MOL T1 T2] PerBox_Based_Rate=Rate; impvar PerBox_Based_Costs_CYS {i in ISN} = sum {b in BOX,m in {'CYS'}} PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,'CYS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b]; set <str,str> PerBox_Based_Rate_NoZeroes_CFS; num PerBox_Based_Rate_CFS {PerBox_Based_Rate_NoZeroes_CFS}; read data CASUSER.BOXRATE (where=(RateBasis="PerBox" and Ratetype='Linehaul' and MOL='CFS' and Rate>0)) into PerBox_Based_Rate_NoZeroes_CFS=[BOX T2] PerBox_Based_Rate_CFS=Rate; impvar PerBox_Based_Costs_CFS {w in T2} = sum {b in BOX} PerBox_Based_Rate_CFS[b,w] * BoxesNeeded_CFS[b,w]; impvar TotalBoxBasedCost = sum {i in ISN} PerBox_Based_Costs_CYS[i] + sum {w in T2} PerBox_Based_Costs_CFS[w]; print PerBox_Based_Rate_CFS PerBox_Based_Rate; /*Box Based Costs END */ /*Vol Based Costs START*/ set <str,str,str,str,str> PerVol_Based_Rate_NoZeroes; num PerVol_Based_Rate {PerVol_Based_Rate_NoZeroes} init 0; read data CASUSER.BOXRATE (where=(RateBasis="PerVolUOM" and Ratetype='Carrier_BOL' and Rate>=0)) into PerVol_Based_Rate_NoZeroes=[ORG DES MOL T1 T2] PerVol_Based_Rate=Rate; impvar VolBasedCost {i in ISN} =Volume_ISN[i]* sum {m in MOL} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]]*Is_ISN_MOL[i,m] ; impvar TotalVolBasedCost = sum {i in ISN} VolBasedCost[i]; /*Vol Based Costs END*/ /*Shipment Based Costs START*/ set <str,str,str,str,str> PerShp_Based_Rate_NoZeroes; num PerShp_Based_Rate {PerShp_Based_Rate_NoZeroes}; read data CASUSER.BOXRATE (where=(RateBasis="PerShipment" and Ratetype='Export_Declaration' and Rate>=0)) into PerShp_Based_Rate_NoZeroes=[ORG DES MOL T1 T2] PerShp_Based_Rate=Rate; impvar ShipmentBasedCost {i in ISN} = sum {m in MOL} PerShp_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m]; impvar TotalShipmentBasedCost = sum {i in ISN} ShipmentBasedCost[i]; print PerBox_Based_Rate PerBox_Based_Rate_CFS PerVol_Based_Rate PerShp_Based_Rate ; /*Shipment Based Costs END*/ /*Define Rates and ImplicitVariables END*/ Min TotalCost = TotalBoxBasedCost + TotalVolBasedCost + TotalShipmentBasedCost; /* Constraints START*/ con OneMOL{i in ISN}: sum{m in MOL} Is_ISN_MOL[i,m] = 1; /* This is to make sure one ISN can go either CYS or CFS */ con BoxConCYS {i in ISN}: sum {b in BOX} Volume_Capacity[b] * BoxesNeeded_CYS[i,b] >= Volume_ISN[i] * Is_ISN_Mol[i,'CYS']; impvar TotalVol {m in MOL, w in T2} = sum {i in ISN} Volume_ISN[i] * Is_ISN_MOL_T2[i,m,w]; con BoxConCFS {w in T2}: sum {b in BOX} Volume_Capacity_CFS[b,w] * BoxesNeeded_CFS[b,w] >= TotalVol['CFS',w]; solve with milp / decomp=(method=concomp); expand; num optCYSCost {ISN}; for {i in ISN} optCYSCost[i] = PerBox_Based_Costs_CYS[i] + VolBasedCost[i] + ShipmentBasedCost[i]; print PerBox_Based_Costs_CYS VolBasedCost ShipmentBasedCost optCYSCost; num volShareCFS {i in ISN,w in T2} = Volume_ISN[i] * Is_ISN_MOL[i,'CFS'].sol / TotalVol['CFS',w].sol; print Is_ISN_MOL; print Is_ISN_MOL_T2; print BoxesNeeded_CYS BoxesNeeded_CFS; print PerBox_Based_Costs_CYS PerBox_Based_Costs_CFS; print volShareCFS VolBasedCost TotalVolBasedCost TotalVol; print ShipmentBasedCost; quit;

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

Rob -

Here is the rate file as mentioned in the earlier comment

Thank you

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

Except for BoxRate.xlsx, it looks like you didn't provide the latest data tables you are using.

To avoid the duplicate key warnings, make sure the columns that appear within the square brackets [] in the READ DATA statement uniquely identify the observation.

The latest code you sent yields errors related to two IMPVAR declarations. Here are two corrections that should help you get further:

```
impvar VolBasedCost {i in ISN} = Volume_ISN[i] *
/* sum {m in MOL} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]]*Is_ISN_MOL[i,m];*/
sum {<Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]> in PerVol_Based_Rate_NoZeroes} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m];
```

```
impvar ShipmentBasedCost {i in ISN} =
/* sum {m in MOL} PerShp_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m];*/
sum {<Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]> in PerShp_Based_Rate_NoZeroes} PerShp_Based_Rate[Org_ISN[i],Des_ISN[i],m,T1_ISN[i],T2_ISN[i]] * Is_ISN_MOL[i,m];
```

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

Rob.

Thank you. The only thing that is new is BoxRate.xlsx. Everything else is the same data from last time. I will use the volbasedcost and Shipmentcost you mentioned and check it out.

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

Rob

I have incorporated the volbased cost and shipment based cost. model runs without errors but the answer is still 173*4= $692.

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

Please supply the tables you are using based on the toy example.

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

Rob

Here are the tables in toy example. will send a couple of more in next one as max allowed is 5.

Also , I mean the full model to just include days and weeks to the three ISNs, 1 week and 2 days that were used in the toy model.

The only difference between toy and full model is that I want to add all indices and still be able to get to $3,622.

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

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

Here is code to replicate the summary info for the toy example:

```
create data SolutionDataCYS from [ISN=i BoxType=b]={i in ISN, b in BOXES: NumBoxesCYS[i,b] > 0.5}
MOL='CYS' Org=Org_ISN[i] Des=Des_ISN[i] T1=T1_ISN[i] T2=T2_ISN[i] Volume=Volume_ISN[i] Weight=Weight_ISN[i]
BoxNeeded=NumBoxesCYS Vol_InsideBox=Volume_ISN[i] VolShare=1
BoxBasedCost=(boxRate[b]*NumBoxesCYS[i,b]) VolBasedCost=0 ShipmentBasedCost=shipmentRate['CYS']
VolCapacity=(volCapacity[b]*NumBoxesCYS[i,b]);
create data SolutionDataCFS(where=(VolShare>0)) from [ISN=i BoxType=b]={i in ISN, b in BOXES: NumBoxesCFS[b] > 0.5}
MOL='CFS' Org=Org_ISN[i] Des=Des_ISN[i] T1=T1_ISN[i] T2=T2_ISN[i] Volume=Volume_ISN[i] Weight=Weight_ISN[i]
BoxNeeded=(volShareCFS[i]*NumBoxesCFS[b]) Vol_InsideBox=Volume_ISN[i] VolShare=volShareCFS[i]
BoxBasedCost=(volShareCFS[i]*boxRate[b]*NumBoxesCFS[b]) VolBasedCost[i] ShipmentBasedCost=0
VolCapacity=(volCapacity[b]*NumBoxesCFS[b]);
quit;
data SolutionData;
set SolutionDataCYS SolutionDataCFS;
Total = BoxBasedCost + VolBasedCost + ShipmentBasedCost;
Utilization = Volume/VolCapacity;
run;
proc print data=SolutionData;
sum _numeric_;
run;
```

Obs | ISN | BoxType | MOL | Org | Des | T1 | T2 | Volume | Weight | BoxNeeded | Vol_InsideBox | VolShare | BoxBasedCost | VolBasedCost | ShipmentBasedCost | VolCapacity | Total | Utilization |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

1 | V1 | 40H | CYS | SHA | ATL | D1 | W1 | 130 | 304 | 2.00000 | 130 | 1.00000 | 2400.00 | 0 | 50 | 138 | 2450.00 | 0.94203 |

2 | V2 | 40F | CFS | SHA | ATL | D1 | W1 | 42 | 491 | 0.97674 | 42 | 0.97674 | 976.74 | 168 | 0 | 60 | 1144.74 | 0.70000 |

3 | V8 | 40F | CFS | SHA | ATL | D2 | W1 | 1 | 2 | 0.02326 | 1 | 0.02326 | 23.26 | 4 | 0 | 60 | 27.26 | 0.01667 |

173 | 797 | 3.00000 | 173 | 2.00000 | 3400.00 | 172 | 50 | 258 | 3622.00 | 1.65870 |

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

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

Rob I was able to figure out what was wrong . The TotalVol impvar that i had in my model was not correct. When i corrected it, everything flowed smoothly. Again, thanks a lot for all your support Rob. You are the best.

I learned a lot , thanks to you. Have a good day

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.