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   ... View more

Rob. thank you this is awesome,. I will use this and change it accordingly in my ouptus. the only one thing pending is for the full model (with all the indices for the 3 ISNs in W1 and 2 days (D1 and D2) to get to $3,622 , the true optimum.  ... View more

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.  ... View more

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

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.      ... View more

Rob - Here is the rate file as mentioned in the earlier comment  Thank you ... View more

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;   ... View more

Rob Thank you very much I will use the updated code and let you know. I will also add the proportion. Will work on the full fledged model and let you know of any issues. The one question i have is on how my point (c) in my earlier comment. How do I write the impvar and constraints correctly? Example : impvar like "PerBox_Based_Costs_CFS" and constraints like "BoxConCFS" in my code, because in your code there is no need of i in ISN, but in the full fledged model, capacity or rate is a function of Org, Des, MOL, Box , T1 and T2. Without i in ISN, i would like to learn how to write the constraints and impvar correctly.  Thanks a ton for all your support as always.  ... View more

Rob (a) This is awesome and exactly aligns with what I had as answer. Cannot thank you enough. I have few points for clarifying The constraint "RationalVendor", it works but I am not able to follow the logic. For each vendor, we are imposing a constraint but I want to learn the thought process behind why you had it multiplied by OptCostCY*Rate*TotalVol['CFS] to make sure this is greater than the Volumebasecost[i]. Can you pls explain this little more so that I can follow. This would allow me to add constraints, if need be, if the rationale needs to be tweaked to add more constraints on shipmentbased costs of any Weight based costs.  A related question would be, if our goal is to make sure the overall network cost is cheaper though each vendor may pay more, how do we modify this? (b) We had a decision Variable called Proportion that can be >0 and <1 that governs that for a given shipment, if the tool says 1X40F and 1X20F, the volume in each box type should add up to 1. Example, if 72 vol, it could be 1x40H(with 69 volume in it) and 1x20F (with 3 volume in it). The proportion would be 69/72 for a 40H and it would be 3/72 for a 20F. We had this constraint in earlier models but not on this. My question is should we add that to this model to make sure the model is splitting the vol and weight right? (c) I have expanded the model (code below but not yet 100% right yet) to accommodate origins, destinations, days and weeks, because for the same lane (origin destination pair) the rate of a 20F. for example can change from quarter to quarter though it is not a general trend, but wanted to have the capability to do so if such situation arises. So to that , I am having some syntax issues when I write some impvar like "PerBox_Based_Costs_CFS"  and constraints  like "BoxConCFS". Basically, Org[i], Des[i], T1_ISN[i] and T2_ISN[i] is what I use to cull out that lane and day, week. But how do I write it the right way withoug having to sum it up across all ISNs.  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 {i in ISN, b in BOX,m in{'CFS'},d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]> in PerBox_Based_Rate_NoZeroes} PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * 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,m,d,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}:/* This is not right*/ 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']; expand BoxConCFS; /* first solve to find optimal CYS costs */ for {i in ISN} fix Is_ISN_Mol[i,'CYS'] = 1; solve with milp / decomp=(method=concomp); num optCYSCost {ISN}; for {i in ISN} optCYSCost[i] = BoxBasedCostCYS[i] + VolBasedCost[i] + ShipmentBasedCost[i]; print BoxBasedCostCYS VolBasedCost ShipmentBasedCost optCYSCost; /* now unfix IsMol and impose constraint that each vendor chooses rationally */ unfix Is_ISN_Mol; con RationalVendor {i in ISN}: VolBasedCost[i] <= optCYSCost[i] * PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * TotalVol['CFS']; /* solve to minimize total cost */ solve; 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; I will also add also add another constraint that says if you are using a 20F, then it needs to be at least this much full , else go to next possible box. Also, after everything is good, I shall create the create data step . ... View more

HI Rob Just sending the toy version again with the 40F change that you were mentioning. ... View more

Rob Thanks for looking into the toy model.  (a) yes. for the case where everything going via CFS,  you are right that 3x40F is the cheapest as 180>173. Totally agree, for a cost of $3,692. That should have been the correct box size and number. I was just quickly doing a sub-optimal answer when i said 3x40H. You were right on that. (b To answer your second question, Vendors do not need to co-operate amongst themselves to minimize the total network cost. Like you said, if a vendor is able to choose CYS because it reduces that vendor's cost even if it makes the overall cost higher, that's fine and that is what we want as a matter of fact. So, it is for the SAS model to figure out which vendor goes which way (CYS of CFS). If it is CYS, then it goes on that day itself with that just vendor's load only. If CYS is not cheaper for a vendor, then it goes to CFS where it gets combined with other similar vendors with same fate, and the ship sails at the end of the week taking all of their loads with again the cheapest mix of assets to ship them.      ... View more

Rob Here is the toy model in excel attached herewith. As you recommended, I have take 1 week, 2 days and 3 vendors. Very small scale model. I have written my model in excel. The input are ISN (SHA_ATL_V1_D1_W1, SHA_ATL_V2_D1_W1 and SHA_ATL_V8_D2_W1) and the weight and volume are the same from what you have. The box specs are the same from what you have. The rates are same from what you have except 1 small difference. I use a $4 per VolUOM instead of $20 so that it allows me to test both CFS and CY. If you can just updated this one number in your Rate Sheet, that is all the change you need. Apart from this change, everything else is same. You will see all these input in the one sheet attached (called "toy").  From cells A1 to U6, I evaluate the costs if each of these ISNs go individually. The total cost of doing this CYS way is $4,350.  Now, cells A9 to U11 assumes that everything gets consolidated at the end of the week and goes CFS way. The cost is $4,292. The next thing that I do is to compare the CFS costs and CYS costs for each ISNs from cells cells A13 to Y16.  As we can see for this ISN "SHA_ATL_V1_D1_W1" CYS costs is $2,450 and CFS costs is expensive ($3,225). For arriving at CFS costs, I take the volume share of this vendor (130/173)= 0.75 and then times it with the total CFS costs of $4,292. If I do this for all three ISNs , we can see that for the other 2 ISNs, CFSWay proves to be cheaper.  With this, what I do, is just cull out "SHA_ATL_V2_D1_W1" and "SHA_ATL_V8_D2_W1" for a consolidated volume of 43 (Cells A19 to X23). For this i find out the number of boxes (manually said 1X40F is fine). and then I have costed it out. So the first ISN that is SHA_ATL_V1_D1_W1, goes via CYS way (that is this vendor ships all by himself in a 2X40H) for a total cost of $2,450 (2x$1,200) + ($50). The other 2 ISNs go via CFS way at the end of the week for cost of $1,145+$27= $1,172 as given in the excel. Columns A19 to X23 gives the answer . We know that if it is a CFS move, then the weight and volume numbers are all aggregated weekly but the #of Boxes and Costs are all prorated(by Volume because CFS gives importance to Vol based costs) accordingly and played back in the output at the ISN level.. I have my SAS Code as well here with . I am able to run the model. Model runs but the answer is not right. . Do we need to run this twice, first to figure out which ISNs are cheaper via CYS and for those ISNs which are deemed "cheaper" to go via CFS ,should the optimum asset determination run again. Am just thinking loud. I am at a cross roads now. Appreciate your help as always. Thank you. 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; NUM BigM; READ DATA CASUSER.BigM INTO BigM=Volume; /*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 and Associated Impvars - START */ var BoxesNeeded_CYS {ISN,BOX,MOL,T1,T2} >= 0 integer; var Proportion_CYS {ISN,BOX,MOL,T1,T2} >= 0 <= 1; var BoxesNeeded_CFS {BOX,MOL,T2} >= 0 integer; var Proportion_CFS {BOX,MOL,T2} >= 0 <= 1; impvar VolumeInsideBox_CYS {i in ISN, b in BOX,m in {'CYS'},d in T1,w in T2} = Volume_ISN[i] * Proportion_CYS[i,b,m,d,w]; impvar WeightInsideBox_CYS {i in ISN, b in BOX,m in {'CYS'},d in T1,w in T2} = Weight_ISN[i] * Proportion_CYS[i,b,m,d,w]; impvar VolumeInsideBox_CFS {b in BOX,m in {'CFS'},w in T2} = sum {i in ISN} Volume_ISN[i] * Proportion_CFS[b,m,w]; impvar WeightInsideBox_CFS {b in BOX,m in {'CFS'},w in T2} = sum {i in ISN} Weight_ISN[i] * Proportion_CFS[b,m,w]; var Is_ISN_MOL {ISN,MOL} binary; var Is_MOL {MOL} binary; /* Decision Variable and Associated Impvars - END */ /*Define Rates and Implicit Variables - 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 = sum {i in ISN, b in BOX,m in MOL,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,m,d,w]; impvar PerBox_Based_Costs_CFS = sum {i in ISN, b in BOX,m in MOL,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_CFS[b,m,w]; 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 PerVol_Based_Costs_CYS = sum {i in ISN, b in BOX, m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerVol_Based_Rate_NoZeroes} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * VolumeInsideBox_CYS[i,b,m,d,w]; impvar PerVol_Based_Costs_CFS = sum {i in ISN, b in BOX, m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerVol_Based_Rate_NoZeroes} PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * VolumeInsideBox_CFS[b,m,w]; set <str,str,str,str,str,str> PerShp_Based_Rate_NoZeroes_Entry; num PerShp_Based_Rate_Entry {PerShp_Based_Rate_NoZeroes_Entry}; read data CASUSER.BOXRATE (where=(RateBasis="PerShipment" and Ratetype='Export_Declaration' and Rate>0)) into PerShp_Based_Rate_NoZeroes_Entry=[ORG DES MOL BOX T1 T2] PerShp_Based_Rate_Entry=Rate; impvar PerShp_Based_Costs_Entry= sum {i in ISN, b in BOX, m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerShp_Based_Rate_NoZeroes_Entry} PerShp_Based_Rate_Entry[Org_ISN[i],Des_ISN[i],b,m,d,w]; /*Define Implicit Variables - END */ Min TotalCost = PerBox_Based_Costs_CYS + PerVol_Based_Costs_CYS + PerBox_Based_Costs_CFS + PerVol_Based_Costs_CFS + PerShp_Based_Costs_Entry; /*General Constraints */ 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 InEqualOut_Volume: sum {i in ISN, b in BOX, m in MOL, d in T1, w in T2} VolumeInsideBox_CYS[i,b,'CYS',T1_ISN[i],T2_ISN[i]] + sum {i in ISN, b in BOX, m in MOL, w in T2} VolumeInsideBox_CFS[b,'CFS',T2_ISN[i]] = sum {i in ISN, m in MOL}Volume_ISN[i]*Is_ISN_MOL[i,m]; con InEqualOut_Weight: sum {i in ISN, b in BOX, m in MOL, d in T1, w in T2} WeightInsideBox_CYS[i,b,'CYS',T1_ISN[i],T2_ISN[i]] + sum {i in ISN, b in BOX, m in MOL, w in T2} WeightInsideBox_CFS[b,'CFS',T2_ISN[i]] = sum {i in ISN, m in MOL}Weight_ISN[i]*Is_ISN_MOL[i,m]; /*End of General Constraints */ /*CYS Specific Constraints */ 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,m,d,w] = 0; con SumProportion_ToOne_CYS {i in ISN,m in {'CYS'}}: sum {b in BOX} Proportion_CYS[i,b,'CYS',T1_ISN[i],T2_ISN[i]] <= 1; con Volume_Constraint_CYS {i in ISN, b in BOX, m in {'CYS'}}: Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] >= VolumeInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]; con Volume_Constraint_MinThresh_CYS {i in ISN, b in BOX, m in {'CYS'}}: Volume_Min[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] <= VolumeInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]; con Weight_Constraint_CYS {i in ISN, b in BOX, m in {'CYS'}}: Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] >= WeightInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]; con Weight_Constraint_MinThresh_CYS {i in ISN, b in BOX, m in {'CYS'}}: Weight_Min[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] <= WeightInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]; con CYSSpecific_Volume {i in ISN,m in {'CYS'}}: sum{b in BOX} VolumeInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]=Volume_ISN[i]*Is_ISN_MOL[i,'CYS']; con CYSSpecific_Weight {i in ISN,m in {'CYS'}}: sum{b in BOX} WeightInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]=Weight_ISN[i]*Is_ISN_MOL[i,'CYS']; /* End of CYS Specific Constraints*/ /* CFS Specific Constraints*/ 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,m,w] = 0; con SumProportion_ToOne_CFS {w in T2}: sum {b in BOX, m in {'CFS'}} Proportion_CFS[b,'CFS',w] <= 1; con Volume_Constraint_CFS {b in BOX, m in {'CFS'},w in T2}: sum{i in ISN}Volume_Capacity[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,'CFS',w] >= VolumeInsideBox_CFS[b,'CFS',w]; con Weight_Constraint_CFS {b in BOX, m in {'CFS'},w in T2}: sum {i in ISN} Weight_Capacity[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,'CFS',w] >= WeightInsideBox_CFS[b,'CFS',w]; con Volume_Constraint_MinThresh_CFS {b in BOX, m in {'CFS'},w in T2}: sum{i in ISN }Volume_Min[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,'CFS',w] <= VolumeInsideBox_CFS[b,'CFS',w]; con Weight_Constraint_MinThresh_CFS {b in BOX, m in {'CFS'},w in T2}: sum{i in ISN }Weight_Min[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,'CFS',w] <= WeightInsideBox_CFS[b,'CFS',w]; con CFSSpecific_Volume {w in T2, m in {'CFS'}}: sum{b in BOX} VolumeInsideBox_CFS[b,'CFS',w]<=T2_Volume[w]*Is_MOL['CFS']; con CFSSpecific_Weight {w in T2, m in {'CFS'}}: sum{b in BOX} WeightInsideBox_CFS[b,'CFS',w]<=T2_Weight[w]*Is_MOL['CFS']; solve with milp; print Is_ISN_MOL; print IS_MOL; print PerBox_Based_Costs_CYS; print PerBox_Based_Costs_CFS; print PerVol_Based_Costs_CYS; print PerVol_Based_Costs_CFS; print PerShp_Based_Costs_Entry; print BoxesNeeded_CFS; print BoxesNeeded_CYS; print PerBox_Based_Rate;   ... View more

Rob. I will parallelly work on a smaller instance like u mentioned a week and 2 days and write a informal model in excel and see ... View more