Hi Rob
This is continuation of the problem that you helped to solve. I am doing some tweaks. I have attached my dataset here. I am specifically looking for ISN='SHA_SEA_10' that has a volume of 30 and Weight of 100. All Box types are available for this lane (SHA-SEA). The model gives the answer as all LCL with a cost of $930 ($31 x 30 Volume) . But there is another solution that is cheaper than that:
Load the 20 F with it maximum, that is 29 Vol. The remaining 1 CBM in LCL. This way we get (1x880) + (1X31)=$911 that is cheaper that what model says. But the way I set up model does not get to it because of reasons that I can't figure out. As a side note I get this warning message : WARNING: The Decomposition algorithm was not run because the automated method was unable to find block-angular form.
Here is 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];
/*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];
set <str,str,str> PerVol_Based_Rate_NoZeroes;
num PerVol_Based_Rate {PerVol_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE
(where=(RateBasis="PerVolUOM" and Ratetype='Linehaul' and Rate>0))
into PerVol_Based_Rate_NoZeroes=[ORG DES BOX] PerVol_Based_Rate=Rate;
impvar PerVol_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerVol_Based_Rate_NoZeroes}
PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * Vol_ISN[i]* BoxesNeeded[i,b];
/*Define Implicit Variables - END */
print PerBox_Based_Rate;
Min TotalCost = PerBox_Based_Costs + PerVol_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;
Hi Rob
I did the 2 changes that you did. Model seems to work. I tried multiple Volumes and checked my hand calculations with SAS and it does match. Thanks a lot for the suggestions that you mentioned. It worked perfect.
While at it, I want to see in my outputs the PerBox Cost and Per Vol costs so that I can play it back to the end user. I understand that for the modeling we have omitted Vol_ISN[i] for PerVolbased costs. This helps to get to the right answer directionally. But when i want to play it back , i want the PerVol based costs to include Vol_ISN[i] in my outputs. Here is what I have in my Create data step at the end of the model. Can you help me to get the right syntax.
create data CASUSER.OptMix_RESULTS (where=(BoxesNeeded > 0.0000001)) from
[ISN=i BOX=b]
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
BoxBasedCosts=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * sum (BoxesNeeded [i,b]))
VolBasedCosts=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * Vol_ISN*BoxesNeeded[i,b]);
;
The BoxBasedCosts and VolBasedCosts above - I think i did not do it correctly because for BoxBasedCosts, there is no LCL and so how do I specify it inside the create data step to include only those where we have a rate for PerBoxRate, similar to how we declared:
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];
The same case for PerVol_Based_Costs as well how to do that correctly in create data step.
Another thing that I would like to see in output table, is in this sample shipment that we used ISN='SHA_SEA_10' with 30 Volume, how much volume was stuffed in 20F container and how much volume was stuffed in LCL. This will be useful when I measure utilization of Boxes later on.
The last two steps that I am going to try to get this model to close is
(a) I am going to add another cost category, that is Wt based cost but the logic is same.
(b) will add Vol and Wt Max Capacities, so that a shipment does not exceed a given box's max limits. Will try these two and let you know if I face problems.
The link to the original probem is here
Please double-check your data and code. I get a different log:
NOTE: Problem generation will use 4 threads. NOTE: The problem has 225 variables (0 free, 12 fixed). NOTE: The problem uses 2 implicit variables. NOTE: The problem has 12 binary and 213 integer variables. NOTE: The problem has 50 linear constraints (0 LE, 0 EQ, 50 GE, 0 range). NOTE: The problem has 250 linear constraint coefficients. NOTE: The problem has 0 nonlinear constraints (0 LE, 0 EQ, 0 GE, 0 range). NOTE: The initial MILP heuristics are applied. NOTE: The MILP presolver value AUTOMATIC is applied. NOTE: The MILP presolver removed 140 variables and 22 constraints. NOTE: The MILP presolver removed 148 constraint coefficients. NOTE: The MILP presolver modified 4 constraint coefficients. NOTE: The presolved problem has 85 variables, 28 constraints, and 102 constraint coefficients. NOTE: The MILP solver is called. NOTE: The Decomposition algorithm is used. NOTE: The Decomposition algorithm is executing in single-machine mode. NOTE: The DECOMP method value CONCOMP is applied. NOTE: The decomposition identification used 0.00 (cpu: 0.00) seconds. NOTE: The problem has a decomposable structure with 24 blocks. The largest block covers 7.143% of the constraints in the problem. NOTE: The decomposition subproblems cover 85 (100%) variables and 28 (100%) constraints. NOTE: The deterministic parallel mode is enabled. NOTE: The Decomposition algorithm is using up to 4 threads. Iter Best Master Best LP IP CPU Real Bound Objective Integer Gap Gap Time Time 1 51842.0000 51842.0000 51842.0000 0.00% 0.00% 0 0 Node Active Sols Best Best Gap CPU Real Integer Bound Time Time 0 1 5 51842.0000 51842.0000 0.00% 0 0 NOTE: The Decomposition algorithm used 4 threads. NOTE: The Decomposition algorithm time is 0.26 seconds. NOTE: Optimal. NOTE: Objective = 51842.
oops. my bad. let me check the data and the code . get back to you on this thanks a lot
Hi Rob
I have attached the dataset herewith. This has just only 1 ISN (Shipment) that I was experimenting. The SAS code is same. No change.
The BOXrates and BOXspecs are also there in the file. The 30 Volume, if we can ship 1x20F (29 Volume in this 20F BOX) for a cost of $880 and the remaining 1 x LCL for a cost of $1X31=$31 the total would be $880+$31=$911. The model says $930 with everything shipped as LCL. What changes should we make to get the model to chose the lowest cost option?
Below is my log:
WARNING: The Decomposition algorithm was not run because the automated method was unable to find block-angular form.
NOTE: The Branch and Cut algorithm is used.
Node Active Sols BestInteger BestBound Gap Time
0 1 1 930.0000000 910.3448276 2.16% 0
NOTE: Optimal.
NOTE: Objective = 930.
Here is the code anycase:
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];
set <str,str,str> PerVol_Based_Rate_NoZeroes;
num PerVol_Based_Rate {PerVol_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE
(where=(RateBasis="PerVolUOM" and Ratetype='Linehaul' and Rate>0))
into PerVol_Based_Rate_NoZeroes=[ORG DES BOX] PerVol_Based_Rate=Rate;
impvar PerVol_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerVol_Based_Rate_NoZeroes}
PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * Vol_ISN[i]* BoxesNeeded[i,b];
/*Define Implicit Variables - END */
print PerBox_Based_Rate;
Min TotalCost = PerBox_Based_Costs + PerVol_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;
I recommend two changes.
1. Change the PerVol_Based_Costs declaration to omit Vol_ISN[i]:
impvar PerVol_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerVol_Based_Rate_NoZeroes}
/* PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * Vol_ISN[i] * BoxesNeeded[i,b];*/
PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
2. Change Volume_Capacity from 10000 to 1 when Box = LCL in the BoxSpecs table.
With these changes, each LCL box from SHA to SEA contributes 31 to the objective and 1 unit to the volume constraint, as you can see from the EXPAND output. The resulting optimal solution matches what you expected:
Solution Summary | |
---|---|
Solver | MILP |
Algorithm | Branch and Cut |
Objective Function | TotalCost |
Solution Status | Optimal |
Objective Value | 911 |
Relative Gap | 0 |
Absolute Gap | 0 |
Primal Infeasibility | 0 |
Bound Infeasibility | 0 |
Integer Infeasibility | 0 |
Best Bound | 911 |
Nodes | 1 |
Solutions Found | 2 |
Iterations | 5 |
Presolve Time | 0.00 |
Solution Time | 0.01 |
BoxesNeeded | |||||||||
---|---|---|---|---|---|---|---|---|---|
20F | 40F | 40H | 45F | Air_DEF_LD | Air_DEF_MD | Air_STD_LD | Air_STD_MD | LCL | |
SHA_SEA_10 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
Hi Rob
I did the 2 changes that you did. Model seems to work. I tried multiple Volumes and checked my hand calculations with SAS and it does match. Thanks a lot for the suggestions that you mentioned. It worked perfect.
While at it, I want to see in my outputs the PerBox Cost and Per Vol costs so that I can play it back to the end user. I understand that for the modeling we have omitted Vol_ISN[i] for PerVolbased costs. This helps to get to the right answer directionally. But when i want to play it back , i want the PerVol based costs to include Vol_ISN[i] in my outputs. Here is what I have in my Create data step at the end of the model. Can you help me to get the right syntax.
create data CASUSER.OptMix_RESULTS (where=(BoxesNeeded > 0.0000001)) from
[ISN=i BOX=b]
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
BoxBasedCosts=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * sum (BoxesNeeded [i,b]))
VolBasedCosts=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * Vol_ISN*BoxesNeeded[i,b]);
;
The BoxBasedCosts and VolBasedCosts above - I think i did not do it correctly because for BoxBasedCosts, there is no LCL and so how do I specify it inside the create data step to include only those where we have a rate for PerBoxRate, similar to how we declared:
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];
The same case for PerVol_Based_Costs as well how to do that correctly in create data step.
Another thing that I would like to see in output table, is in this sample shipment that we used ISN='SHA_SEA_10' with 30 Volume, how much volume was stuffed in 20F container and how much volume was stuffed in LCL. This will be useful when I measure utilization of Boxes later on.
The last two steps that I am going to try to get this model to close is
(a) I am going to add another cost category, that is Wt based cost but the logic is same.
(b) will add Vol and Wt Max Capacities, so that a shipment does not exceed a given box's max limits. Will try these two and let you know if I face problems.
I'm glad it worked for you. Please accept my answer and then open a new question for the CREATE DATA step.
Rob thanks a lot. sorry i forgot to reply and do the "Accept Solution". Was too excited to see it work 🙂
have opened a new ticket for create data step. btw, i tried minimum threshold constraints. seems to be working . checking with diff combo of vt and vol. so far so good. thanks always
Hi @Santha: If you actually wanted to accept Rob's answer rather than your own post, this can be corrected easily: Select his post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.