Hi Rob
This is the create data step , continued from AssetMixproblem.
I want to see in my outputs the PerBox Cost and Per Vol costs so that I can play it back to the end user. I understand that for the modeling we have omitted Vol_ISN[i] for PerVolbased costs. This helps to get to the right answer directionally. But when i want to play it back , i want the PerVol based costs to include Vol_ISN[i] in my outputs. Here is what I have in my Create data step at the end of the model. Can you help me to get the right syntax.
create data CASUSER.OptMix_RESULTS (where=(BoxesNeeded > 0.0000001)) from
[ISN=i BOX=b]
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
BoxBasedCosts=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * sum (BoxesNeeded [i,b]))
VolBasedCosts=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * Vol_ISN*BoxesNeeded[i,b]);
;
The BoxBasedCosts and VolBasedCosts above - I think i did not do it correctly because for BoxBasedCosts, there is no LCL and so how do I specify it inside the create data step to include only those where we have a rate for PerBoxRate, similar to how we declared:
impvar PerBox_Based_Costs = sum {i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b];
The same case for PerVol_Based_Costs as well how to do that correctly in create data step.
Another thing that I would like to see in output table, is in this sample shipment that we used ISN='SHA_SEA_10' with 30 Volume, how much volume was stuffed in 20F container and how much volume was stuffed in LCL. This will be useful when I measure utilization of Boxes later on.
I think the following two CREATE DATA statements do what you want:
create data OptMix_RESULTS_box (where=(BoxesNeeded > 0.05)) from
[ISN=i BOX=b]={i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerBox_Based_Rate_NoZeroes}
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
BoxBasedCosts=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
VolumeUsed=(Volume_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
;
create data OptMix_RESULTS_vol (where=(BoxesNeeded > 0.05)) from
[ISN=i BOX=b]={i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerVol_Based_Rate_NoZeroes}
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
VolBasedCosts=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
VolumeUsed=(Volume_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
;
I think the following two CREATE DATA statements do what you want:
create data OptMix_RESULTS_box (where=(BoxesNeeded > 0.05)) from
[ISN=i BOX=b]={i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerBox_Based_Rate_NoZeroes}
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
BoxBasedCosts=(PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
VolumeUsed=(Volume_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
;
create data OptMix_RESULTS_vol (where=(BoxesNeeded > 0.05)) from
[ISN=i BOX=b]={i in ISN, b in BOX: <Org_ISN[i],Des_ISN[i],b> in PerVol_Based_Rate_NoZeroes}
Org=ORG_ISN[i]
Des=DES_ISN[i]
ShipmentVolume=Vol_ISN[i]
ShipmentWeight=Wt_ISN[i]
BoxesNeeded
Volume_Capacity[Org_ISN[i],Des_ISN[i],b]
Wt_Capacity[Org_ISN[i],Des_ISN[i],b]
VolBasedCosts=(PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
VolumeUsed=(Volume_Capacity[Org_ISN[i],Des_ISN[i],b] * BoxesNeeded[i,b])
;
OptMix_RESULTS_vol
Hi Rob
Thank you. I was thinking of having one table but what you have is great. one for each type. then can i merge them together in 1 table (with box based and vol based sitting in the same column called Costs and Costtype to distinhuis if it is box or vol probably?
OptMix_RESULTS_box
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.