BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Santha
Pyrite | Level 9

Hi Rob

I am creating a new thread, because of many replies to the old one. This is just to be clear.  The original thread is here. Here is my latest code at the end of this. The data remains the same.

I do not have any decomp in my solve statement.  The specific questions that I have are

(a) I do not see  any outputs for T2='W2', though I have it in my input data. 

(b) Want to make sure if an ISN goes to CYS, then no other ISN gets clubbed with it. If an ISN goes via CFS, then it will go together with other similar ISNs, at the end of the week for that consolidated volume and weight. 

(c) I do not have any BigM constraints. Is that ok? I have a InEqualOut_Volume.

(d) In the create data step, I do not see any observations for "OptMix_RESULTS_Volume" and "OptMix_RESULTS_Shipment", though there are costs.   

 

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;


/*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 {ISN,BOX,MOL,T1,T2} >= 0 integer;
var Proportion {ISN,BOX,MOL,T1,T2} >= 0 <= 1;

impvar VolumeInsideBox {i in ISN, b in BOX,m in MOL,d in T1,w in T2} = Volume_ISN[i] * Proportion[i,b,m,d,w];
impvar WeightInsideBox {i in ISN, b in BOX,m in MOL,d in T1,w in T2} = Weight_ISN[i] * Proportion[i,b,m,d,w];


NUM BigM;
READ DATA CASUSER.BigM INTO BigM=Volume;

var Is_ISN_MOL {ISN,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 = 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[i,b,m,d,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 = 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[i,b,m,d,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 */
print PerShp_Based_Rate_Entry;

Min TotalCost = PerBox_Based_Costs + PerVol_Based_Costs+PerShp_Based_Costs_Entry;


/* Constraints - to chose box type and number*/

   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[i,b,m,d,w] = 0;

	  con SumProportionToOne {i in ISN}:
      sum {b in BOX,m in MOL,d in T1,w in T2} Proportion[i,b,m,d,w] = 1;

      con Volume_Constraint {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] >= VolumeInsideBox[i,b,m,d,w];

 	  con Volume_Constraint_MinThreshold {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:
      Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] <= VolumeInsideBox[i,b,m,d,w];

	  con Weight_Constraint {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] >= WeightInsideBox[i,b,m,d,w];

      con Weight_Constraint_MinThreshold {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:
      Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] <= WeightInsideBox[i,b,m,d,w];

/*End of Constraints relating to chosing box type and number*/ 
  	
/* Constraint to enforce other stuff*/ 
	 con OneMOL{i in ISN}:
     sum{M in MOL} Is_ISN_MOL[i,m] = 1;

/* CYS Specific Constraints - */
con CYSSpecific_Volume {i in ISN,m in {'CYS'}}:
sum{b in BOX} VolumeInsideBox[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[i,b,m,T1_ISN[i],T2_ISN[i]]=Weight_ISN[i]*Is_ISN_MOL[i,'CYS'];

/* CFS Specific Constraints*/	
con CFSSpecific_Volume {w in T2, m in {'CFS'}}:
sum{i in ISN, b in BOX} VolumeInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum{i in ISN}Volume_ISN[i]*Is_ISN_MOL[i,'CFS'];

con CFSSpecific_Weight {w in T2, m in {'CFS'}}:
sum{i in ISN, b in BOX} WeightInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum{i in ISN}Weight_ISN[i]*Is_ISN_MOL[i,'CFS'];

con InEqualOut_Volume {i in ISN}:
sum{b in BOX, m in MOL} VolumeInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum {m in MOL}Volume_ISN[i]*Is_ISN_MOL[i,m];


con InEqualOut_Weight {i in ISN}:
sum{b in BOX, m in MOL} WeightInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum {m in MOL}Weight_ISN[i]*Is_ISN_MOL[i,m];

solve with milp; 
print VolumeInsideBox;

create data OptMix_RESULTS_Volume (where=(Proportion > 0)) from
      [ISN=i BOX=b MOL=m T1=d T2=w]={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}
      Org=ORG_ISN[i]
      Des=DES_ISN[i]
	  CustomerName='xx'	  
	  ScenarioName='v'
      ShipmentVolume=Volume_ISN[i]
      ShipmentWeight=Weight_ISN[i]
      BoxesNeeded
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
 	  VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
	  WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Costs=VolumeInsideBox[i,b,m,d,w]
	  DimsType='Vol'
	  Proportion=Proportion[i,b,m,d,w]
	  VolumeInsideBox
	  WeightInsideBox
	;

create data OptMix_RESULTS_Box(where=(Proportion > 0)) from
      [ISN=i BOX=b MOL=m T1=d T2=w]={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}
      Org=ORG_ISN[i]
      Des=DES_ISN[i]
	  CustomerName='xx'	  
	  ScenarioName='v'
      ShipmentVolume=Volume_ISN[i]
      ShipmentWeight=Weight_ISN[i]
      BoxesNeeded
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
 	  VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
	  WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Costs=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w]* BoxesNeeded[i,b,m,d,w])
	  DimsType='Box'
	  Proportion=Proportion[i,b,m,d,w]
	  VolumeInsideBox
	  WeightInsideBox
	;
create data OptMix_RESULTS_Shipment(where=(Proportion > 0)) from
      [ISN=i BOX=b MOL=m T1=d T2=w]={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}
      Org=ORG_ISN[i]
      Des=DES_ISN[i]
	  CustomerName=&CUST	  
	  ScenarioName=&SCNAME
      ShipmentVolume=Volume_ISN[i]
      ShipmentWeight=Weight_ISN[i]
      BoxesNeeded
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
 	  VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
	  WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Costs=(PerShp_Based_Rate_Entry[Org_ISN[i],Des_ISN[i],b,m,d,w])
	  DimsType='SHP'
	  Proportion=Proportion[i,b,m,d,w]
	  VolumeInsideBox
	  WeightInsideBox
	  
;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

Here is code for the toy example to illustrate the approach.  The idea is to solve twice (as you suspected): once to get the optimal CYS costs and then once to solve the whole problem with additional indicator constraints that ensure that each vendor acts rationally (chooses CYS if doing so is cheaper).

 

You will need to add indices for the days and weeks, but I hope this code makes the idea clear.

 

proc optmodel;
   set BOXES = /'20F' '40F' '40H'/;
   num boxRate {BOXES} = [800 1000 1200];
   num volCapacity {BOXES} = [14 60 69];
   set MOL = /CYS CFS/;
   num volRate {MOL} = [0 4];
   num shipmentRate {MOL} = [50 0];
   set ISN = /V1 V2 V8/;
   num vol_ISN {ISN} = [130 42 1];
   num wt_ISN {ISN} = [304 491 2];

   var IsMol {ISN, MOL} binary;
   var NumBoxesCYS {i in ISN, b in BOXES} integer >= 0 <= ceil(vol_ISN[i] / volCapacity[b]);
   var NumBoxesCFS {b in BOXES}           integer >= 0 <= ceil(sum {i in ISN} vol_ISN[i] / volCapacity[b]);

   impvar BoxBasedCostCYS {i in ISN} = sum {b in BOXES} boxRate[b] * NumBoxesCYS[i,b];
   impvar BoxBasedCostCFS = sum {b in BOXES} boxRate[b] * NumBoxesCFS[b];
   impvar TotalBoxBasedCost = sum {i in ISN} BoxBasedCostCYS[i] + BoxBasedCostCFS;
   impvar VolBasedCost {i in ISN} = vol_ISN[i] * sum {m in MOL} volRate[m] * IsMol[i,m];
   impvar TotalVolBasedCost = sum {i in ISN} VolBasedCost[i];
   impvar ShipmentBasedCost {i in ISN} = sum {m in MOL} shipmentRate[m] * IsMol[i,m];
   impvar TotalShipmentBasedCost = sum {i in ISN} ShipmentBasedCost[i];
   min TotalCost = TotalBoxBasedCost + TotalVolBasedCost + TotalShipmentBasedCost;

   con OneMol {i in ISN}:
      sum {m in MOL} IsMol[i,m] = 1;

   con BoxConCYS {i in ISN}:
      sum {b in BOXES} volCapacity[b] * NumBoxesCYS[i,b] >= vol_ISN[i] * IsMol[i,'CYS'];

   impvar TotalVol {m in MOL} = sum {i in ISN} vol_ISN[i] * IsMol[i,m];
   con BoxConCFS:
      sum {b in BOXES} volCapacity[b] * NumBoxesCFS[b] >= TotalVol['CFS'];

   /* first solve to find optimal CYS costs */
   for {i in ISN} fix IsMol[i,'CYS'] = 1;
   solve;
   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 IsMol;
   con RationalVendor {i in ISN}:
       IsMol[i,'CFS'] = 1 implies
       vol_ISN[i] * (BoxBasedCostCFS + sum {j in ISN} (volRate['CFS'] * vol_ISN[j] + shipmentRate['CFS']) * IsMol[j,'CFS'])
       <= optCYSCost[i] * TotalVol['CFS'];

   /* solve to minimize total cost */
   solve;

   num volShareCFS {i in ISN} = vol_ISN[i] * IsMol[i,'CFS'].sol / TotalVol['CFS'].sol;

   print IsMol;
   print NumBoxesCYS NumBoxesCFS;
   print BoxBasedCostCYS BoxBasedCostCFS;
   print volShareCFS VolBasedCost TotalVolBasedCost TotalVol;
   print ShipmentBasedCost;
quit;

View solution in original post

19 REPLIES 19
RobPratt
SAS Super FREQ

With the additional data you provided, I am able to run the code now.  The third CREATE DATA statement yields an error because the macro variables CUST and SCNAME are not defined, so I have just commented those two lines out for now.

 

The log shows several warnings related to the READ DATA for InputData_AssetMix:

0      read data CASUSER.InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES
30 ! Volume_ISN=Volume Weight_ISN=Weight T1_ISN=T1 T2_ISN=T2;
WARNING: Duplicate key <SHA_ATL_V1_D1_W1> was read at observation 14.
WARNING: Duplicate key <SHA_ATL_V2_D1_W1> was read at observation 15.
WARNING: Duplicate key <SHA_ATL_V3_D1_W1> was read at observation 16.
WARNING: Duplicate key <SHA_ATL_V4_D1_W1> was read at observation 17.
WARNING: Duplicate key <SHA_ATL_V5_D1_W1> was read at observation 18.
WARNING: Duplicate key <SHA_ATL_V6_D1_W1> was read at observation 19.
WARNING: Duplicate key <SHA_ATL_V7_D1_W1> was read at observation 20.
WARNING: Duplicate key <SHA_ATL_V8_D1_W1> was read at observation 21.
WARNING: Duplicate key <SHA_ATL_V9_D1_W1> was read at observation 22.
WARNING: Duplicate key <SHA_ATL_V10_D1_W1> was read at observation 23.
WARNING: Duplicate key <SHA_ATL_V11_D1_W1> was read at observation 24.
WARNING: Duplicate key <SHA_ATL_V12_D1_W1> was read at observation 25.
WARNING: Duplicate key <SHA_ATL_V13_D1_W1> was read at observation 26.
NOTE: 13 duplicate keys were read.
NOTE: There were 26 observations read from the data set CASUSER.INPUTDATA_ASSETMIX.

You are using the ISN column as a key, but the last 13 observations have the same values of ISN as the first 13 observations.  If you add a PUT ISN= statement after this, you can see that only 13 observations are used:

ISN={'SHA_ATL_V1_D1_W1','SHA_ATL_V2_D1_W1','SHA_ATL_V3_D1_W1','SHA_ATL_V4_D1_W1',
'SHA_ATL_V5_D1_W1','SHA_ATL_V6_D1_W1','SHA_ATL_V7_D1_W1','SHA_ATL_V8_D1_W1','SHA_ATL_V9_D1_W1',
'SHA_ATL_V10_D1_W1','SHA_ATL_V11_D1_W1','SHA_ATL_V12_D1_W1','SHA_ATL_V13_D1_W1'}

If you print T1_ISN and T2_ISN, you can see that it is the last 13 observations (exactly the ones with T2 = W2):

[1] T1_ISN T2_ISN
SHA_ATL_V10_D1_W1 D6 W2
SHA_ATL_V11_D1_W1 D6 W2
SHA_ATL_V12_D1_W1 D6 W2
SHA_ATL_V13_D1_W1 D6 W2
SHA_ATL_V1_D1_W1 D4 W2
SHA_ATL_V2_D1_W1 D4 W2
SHA_ATL_V3_D1_W1 D5 W2
SHA_ATL_V4_D1_W1 D5 W2
SHA_ATL_V5_D1_W1 D5 W2
SHA_ATL_V6_D1_W1 D5 W2
SHA_ATL_V7_D1_W1 D6 W2
SHA_ATL_V8_D1_W1 D6 W2
SHA_ATL_V9_D1_W1 D6 W2

Furthermore, the values in the T1 and T2 columns in the InputData_AssetMix table do not match the ISN column.  For example, observation 6 has ISN = SHA_ATL_V7_D1_W1 but T1 = D2 rather than D1.  I suspect that the last part of each ISN value should instead match T1 and T2.

 

Before I investigate any further, please confirm whether this table is correct.

Santha
Pyrite | Level 9

Hi Rob

Thank you. The data that you have is not the right one. I did not upload the right one earlier. Now I have attached the right data set herein. Please refer to the attachment here.   I was working on this correct data set where the ISN is all corrected and there won't be any problems with W1, W2 and ISN. Yes. The ISNs would have the T1 and T2 concatenated to it as you have mentioned in your message. I have enclosed the right data set here. The thing to note is that for W1 and W2 the volume and weight are same, the only difference is that the ISNs are different.  

 

In the create data step, the macro variable yeah that may be a problem. But even without that macro variable, where I have just some random string, it is not printing out for OptMix_RESULTS_Vol and  OptMix_RESULTS_Shipment. It is printing out for OptMix_RESULTS_Box. So when I printed out this following

print PerVol_Based_Costs;

print PerBox_Based_Costs;

print PerShp_Based_Costs_Entry;

only PerBox_Based_Costs came with a value of 24,800 and the other two were 0. So I think those two impvar costs are not getting calculated though we have the rates for it. 

I am also not able to figure out why no W2 data is not considered into the model, only W1 is. 

Here is my latest code, the only change being in the create data step (formula for COSTS changed and also landed an error) and printing out the impvar costs.  

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; /*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 {ISN,BOX,MOL,T1,T2} >= 0 integer; var Proportion {ISN,BOX,MOL,T1,T2} >= 0 <= 1; impvar VolumeInsideBox {i in ISN, b in BOX,m in MOL,d in T1,w in T2} = Volume_ISN[i] * Proportion[i,b,m,d,w]; impvar WeightInsideBox {i in ISN, b in BOX,m in MOL,d in T1,w in T2} = Weight_ISN[i] * Proportion[i,b,m,d,w]; NUM BigM; READ DATA CASUSER.BigM INTO BigM=Volume; var Is_ISN_MOL {ISN,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 = 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[i,b,m,d,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 = 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[i,b,m,d,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 + PerVol_Based_Costs + PerShp_Based_Costs_Entry; /* Constraints - to chose box type and number*/    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[i,b,m,d,w] = 0;           con SumProportionToOne {i in ISN}:       sum {b in BOX,m in MOL,d in T1,w in T2} Proportion[i,b,m,d,w] = 1;       con Volume_Constraint {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:       Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] >= VolumeInsideBox[i,b,m,d,w];           con Volume_Constraint_MinThreshold {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:       Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] <= VolumeInsideBox[i,b,m,d,w];           con Weight_Constraint {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:       Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] >= WeightInsideBox[i,b,m,d,w];       con Weight_Constraint_MinThreshold {i in ISN, b in BOX, m in MOL,d in T1,w in T2}:       Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded[i,b,m,d,w] <= WeightInsideBox[i,b,m,d,w]; /*End of Constraints relating to chosing box type and number*/         /* Constraint to enforce other stuff*/          con OneMOL{i in ISN}:      sum{M in MOL} Is_ISN_MOL[i,m] = 1; /* CYS Specific Constraints - */ con CYSSpecific_Volume {i in ISN,m in {'CYS'}}: sum{b in BOX} VolumeInsideBox[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[i,b,m,T1_ISN[i],T2_ISN[i]]=Weight_ISN[i]*Is_ISN_MOL[i,'CYS']; /* CFS Specific Constraints*/    con CFSSpecific_Volume {w in T2, m in {'CFS'}}: sum{i in ISN, b in BOX} VolumeInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum{i in ISN}Volume_ISN[i]*Is_ISN_MOL[i,'CFS']; con CFSSpecific_Weight {w in T2, m in {'CFS'}}: sum{i in ISN, b in BOX} WeightInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum{i in ISN}Weight_ISN[i]*Is_ISN_MOL[i,'CFS']; con InEqualOut_Volume {i in ISN}: sum{b in BOX, m in MOL} VolumeInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum {m in MOL}Volume_ISN[i]*Is_ISN_MOL[i,m]; con InEqualOut_Weight {i in ISN}: sum{b in BOX, m in MOL} WeightInsideBox[i,b,m,T1_ISN[i],T2_ISN[i]]=sum {m in MOL}Weight_ISN[i]*Is_ISN_MOL[i,m]; solve with milp; print PerVol_Based_Costs; print PerBox_Based_Costs; print PerShp_Based_Costs_Entry; create data OptMix_RESULTS_Vol(where=(Proportion > 0)) from       [ISN=i BOX=b MOL=m T1=d T2=w]={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}       Org=ORG_ISN[i]       Des=DES_ISN[i]           CustomerName='xx'                  ScenarioName='v'       ShipmentVolume=Volume_ISN[i]       ShipmentWeight=Weight_ISN[i]       BoxesNeeded       Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]       Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]           VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]           WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]       Costs=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w]* VolumeInsideBox[i,b,m,d,w])           DimsType='Vol'           Proportion=Proportion[i,b,m,d,w]           VolumeInsideBox           WeightInsideBox         ; create data OptMix_RESULTS_Box(where=(Proportion > 0)) from       [ISN=i BOX=b MOL=m T1=d T2=w]={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}       Org=ORG_ISN[i]       Des=DES_ISN[i]           CustomerName='xx'                  ScenarioName='v'       ShipmentVolume=Volume_ISN[i]       ShipmentWeight=Weight_ISN[i]       BoxesNeeded       Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]       Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]           VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]           WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]       Costs=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w]* BoxesNeeded[i,b,m,d,w])           DimsType='Box'           Proportion=Proportion[i,b,m,d,w]           VolumeInsideBox           WeightInsideBox         ; create data OptMix_RESULTS_Shipment(where=(Proportion > 0)) from       [ISN=i BOX=b MOL=m T1=d T2=w]={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}       Org=ORG_ISN[i]       Des=DES_ISN[i]           CustomerName='xx'                  ScenarioName='v'       ShipmentVolume=Volume_ISN[i]       ShipmentWeight=Weight_ISN[i]       BoxesNeeded       Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]       Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]           VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]           WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]       Costs=(PerShp_Based_Rate_Entry[Org_ISN[i],Des_ISN[i],b,m,d,w])           DimsType='SHP'           Proportion=Proportion[i,b,m,d,w]           VolumeInsideBox           WeightInsideBox         ;

 

Santha
Pyrite | Level 9

Here is the correct BoxRate file and BoxSpecs as well, just to make sure we are on the same page. I would request you to use these two files in addition to the Input files that I have sent in my earlier reply. 

 

Santha
Pyrite | Level 9

Hi Rob.  I was able to get one issue resolved. I am able to see both weeks (W1 and W2) in my output.

But there is only calculation of PerBox  costs and PerVol and PerShipment yet. That is one thing.  But the most important thing is to make sure that the constraints are good according to logic and the model is working as per that.  Especially, the CFS logic, where the consolidated weight and volume will be the sum of volumes and weight of all the ISNs that does not go via CYS way. For this consolidated weight and volume at the end of the week going via CFS, we need to figure out the asset mix, not for the indiviudal ISNs. To that extent, should we have another set of decision variables for CFS? Right now it is at the ISN level like below.

var BoxesNeeded {ISN,BOX,MOL,T1,T2} >= 0 integer;
var Proportion {ISN,BOX,MOL,T1,T2} >= 0 <= 1;

 

But for CFS , it shd be consolidated at weekly level for all the remaining ISNs. Not sure how do we do it. Separate decision variables and wt and volume constraints for CFS and CY?  I just tried it in the below code to separate CFS and CY decision variables but I am not sure If I am on the right path. I did not run the model as well. I have attached the new Unique_T2, where I bring in the weekly volume and weight to be used for CFS Constraints. I was just writing the constraints and testing. The CFS constraints especially, I am not sure if it is right.  If we can formulate the model correctly and run it, that would be awesome. Create data step for output is the last piece.

Thanks for your patience and appreciate your support. Thanks. 

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}:
      sum {b in BOX,m in {'CYS'},d in T1,w in T2} Proportion_CYS[i,b,'CYS',d,w] = 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;

create data OptMix_RESULTS_Vol(where=(Proportion_CYS > 0)) from
      [ISN=i BOX=b MOL=m T1=d T2=w]={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}
      Org=ORG_ISN[i]
      Des=DES_ISN[i]
	  CustomerName='xx'	  
	  ScenarioName='v'
      ShipmentVolume=Volume_ISN[i]
      ShipmentWeight=Weight_ISN[i]
      BoxesNeeded
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
 	  VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
	  WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Costs=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w]* VolumeInsideBox_CYS[i,b,m,d,w])
	  DimsType='Vol'
	  Proportion_CYS=Proportion_CYS[i,b,m,d,w]
	  VolumeInsideBox_CYS
	  WeightInsideBox_CYS
	;

create data OptMix_RESULTS_Box(where=(Proportion_CYS > 0)) from
      [ISN=i BOX=b MOL=m T1=d T2=w]={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}
      Org=ORG_ISN[i]
      Des=DES_ISN[i]
	  CustomerName='xx'	  
	  ScenarioName='v'
      ShipmentVolume=Volume_ISN[i]
      ShipmentWeight=Weight_ISN[i]
      BoxesNeeded
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
 	  VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
	  WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Costs=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w]* BoxesNeeded[i,b,m,d,w])
	  DimsType='Box'
	  Proportion_CYS=Proportion_CYS[i,b,m,d,w]
	  VolumeInsideBox_CYS
	  WeightInsideBox_CYS
	;
create data OptMix_RESULTS_Shipment(where=(Proportion_CYS > 0)) from
      [ISN=i BOX=b MOL=m T1=d T2=w]={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}
      Org=ORG_ISN[i]
      Des=DES_ISN[i]
	  CustomerName='xx'	  
	  ScenarioName='v'
      ShipmentVolume=Volume_ISN[i]
      ShipmentWeight=Weight_ISN[i]
      BoxesNeeded
      Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,d,w]
 	  VolumeThresholdMinimum=Volume_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
	  WeightThresholdMinimum=Weight_Min[Org_ISN[i],Des_ISN[i],b,m,d,w]
      Costs=(PerShp_Based_Rate_Entry[Org_ISN[i],Des_ISN[i],b,m,d,w])
	  DimsType='SHP'
	  Proportion_CYS=Proportion_CYS[i,b,m,d,w]
	  VolumeInsideBox_CYS
	  WeightInsideBox_CYS
	  
;
RobPratt
SAS Super FREQ

The error in your new CREATE DATA statement is that you used the wrong index set for the logical condition:

create data OptMix_RESULTS_Vol(where=(Proportion > 0)) from

      [ISN=i BOX=b MOL=m T1=d T2=w]={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}

It should instead be:

create data OptMix_RESULTS_Vol(where=(Proportion > 0)) from

      [ISN=i BOX=b MOL=m T1=d T2=w]={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}

Also, with your latest data, I get an objective value of 49,600 rather than 24,800.

 

But I think these data and syntax issues are distracting from what seems to be your main concern, which is how to mathematically model the problem.  For that purpose, I recommend taking an even smaller instance (maybe one week and two days) and writing out a feasible (not necessarily optimal) plan: how much of what will go where and when, and how much would it cost?  That will help you see what the decision variables, constraints, and objective should be.  After you have constructed the optimization model, converting to PROC OPTMODEL code will be much simpler.

Santha
Pyrite | Level 9

Hi Rob

I just finished writing all the constraints . I broke it down to CFS and CYS. 

THe overall objective is that for a given vendor (which is baked in ISN as V1, V2 etc). we want to know if he can send to CYS daily OR consolidate with other ISNs (thereby vendors) and send to CFS (provided other shipments are also deemed not cost effective to send via CYS). 

Can you look at the code that I have in my above message and let me know please? I did try to spend thinking about the business problem and translating it and thats how i came up with the above logic. Thanks for your patience and help Rob

Thank you. 

 

Santha
Pyrite | Level 9
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
Santha
Pyrite | Level 9

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;

 

RobPratt
SAS Super FREQ

Thank you for providing the toy example, which is very helpful for understanding the problem.  If all 3 shipments use CFS, then 3 boxes of 40F (rather than 40H) would be cheaper and still cover the volume (since 180 >= 173), with a total cost of $3692.  But your third solution is still the cheapest, with total cost $3622.

 

Please clarify whether the vendors cooperate to minimize the overall cost or whether a vendor is able to choose CYS because it reduces that vendor's cost even if it makes the overall cost higher.

Santha
Pyrite | Level 9

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. 

 

 

Santha
Pyrite | Level 9

HI Rob

Just sending the toy version again with the 40F change that you were mentioning.

RobPratt
SAS Super FREQ

Here is code for the toy example to illustrate the approach.  The idea is to solve twice (as you suspected): once to get the optimal CYS costs and then once to solve the whole problem with additional indicator constraints that ensure that each vendor acts rationally (chooses CYS if doing so is cheaper).

 

You will need to add indices for the days and weeks, but I hope this code makes the idea clear.

 

proc optmodel;
   set BOXES = /'20F' '40F' '40H'/;
   num boxRate {BOXES} = [800 1000 1200];
   num volCapacity {BOXES} = [14 60 69];
   set MOL = /CYS CFS/;
   num volRate {MOL} = [0 4];
   num shipmentRate {MOL} = [50 0];
   set ISN = /V1 V2 V8/;
   num vol_ISN {ISN} = [130 42 1];
   num wt_ISN {ISN} = [304 491 2];

   var IsMol {ISN, MOL} binary;
   var NumBoxesCYS {i in ISN, b in BOXES} integer >= 0 <= ceil(vol_ISN[i] / volCapacity[b]);
   var NumBoxesCFS {b in BOXES}           integer >= 0 <= ceil(sum {i in ISN} vol_ISN[i] / volCapacity[b]);

   impvar BoxBasedCostCYS {i in ISN} = sum {b in BOXES} boxRate[b] * NumBoxesCYS[i,b];
   impvar BoxBasedCostCFS = sum {b in BOXES} boxRate[b] * NumBoxesCFS[b];
   impvar TotalBoxBasedCost = sum {i in ISN} BoxBasedCostCYS[i] + BoxBasedCostCFS;
   impvar VolBasedCost {i in ISN} = vol_ISN[i] * sum {m in MOL} volRate[m] * IsMol[i,m];
   impvar TotalVolBasedCost = sum {i in ISN} VolBasedCost[i];
   impvar ShipmentBasedCost {i in ISN} = sum {m in MOL} shipmentRate[m] * IsMol[i,m];
   impvar TotalShipmentBasedCost = sum {i in ISN} ShipmentBasedCost[i];
   min TotalCost = TotalBoxBasedCost + TotalVolBasedCost + TotalShipmentBasedCost;

   con OneMol {i in ISN}:
      sum {m in MOL} IsMol[i,m] = 1;

   con BoxConCYS {i in ISN}:
      sum {b in BOXES} volCapacity[b] * NumBoxesCYS[i,b] >= vol_ISN[i] * IsMol[i,'CYS'];

   impvar TotalVol {m in MOL} = sum {i in ISN} vol_ISN[i] * IsMol[i,m];
   con BoxConCFS:
      sum {b in BOXES} volCapacity[b] * NumBoxesCFS[b] >= TotalVol['CFS'];

   /* first solve to find optimal CYS costs */
   for {i in ISN} fix IsMol[i,'CYS'] = 1;
   solve;
   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 IsMol;
   con RationalVendor {i in ISN}:
       IsMol[i,'CFS'] = 1 implies
       vol_ISN[i] * (BoxBasedCostCFS + sum {j in ISN} (volRate['CFS'] * vol_ISN[j] + shipmentRate['CFS']) * IsMol[j,'CFS'])
       <= optCYSCost[i] * TotalVol['CFS'];

   /* solve to minimize total cost */
   solve;

   num volShareCFS {i in ISN} = vol_ISN[i] * IsMol[i,'CFS'].sol / TotalVol['CFS'].sol;

   print IsMol;
   print NumBoxesCYS NumBoxesCFS;
   print BoxBasedCostCYS BoxBasedCostCFS;
   print volShareCFS VolBasedCost TotalVolBasedCost TotalVol;
   print ShipmentBasedCost;
quit;

Santha
Pyrite | Level 9

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 .

RobPratt
SAS Super FREQ

Sorry for the confusion about the RationalVendor constraint.  I had mistakenly considered only the volume-based costs and neither the box costs nor shipment costs.  I have now updated the code to correctly account for these.  The constraint enforces the rule that, if a vendor uses CFS, the resulting volume-prorated cost for that vendor must be at most the optimal CYS cost for that vendor.  I also imposed explicit upper bounds on the NumBoxesCYS and NumBoxedCFS variables to make the resulting (automatically generated) big-M constraints more numerically stable.

 

If you instead want to allow the vendors to cooperate to minimize overall cost (even it means that some individual vendor might have been better off with CYS), just omit the FIX/UNFIX statements and the RationalVendor constraint and solve once.

 

Yes, you will still need Proportion to account for the weight-based restrictions in your full problem.

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

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.

Discussion stats
  • 19 replies
  • 2153 views
  • 1 like
  • 2 in conversation