Hi All,
I have been solving an airline demand, price and value combination problem with Excel solver pro.
However the input size has grown beyond the limits of excel and therefore I am trying to solve it with SAS OR.
Below is only masked dummy data containing 12 rows. The actual data would have 500,000 rows.
Thanking you in anticipation.
Rgds
Praveen
Text file is attached for easy download and execution. n,
---------------------------------------------------------------------------------------------------------------------
data TABLE1;
input RGN $7. MTH $4. CNTRY $3. CLS $3. MKT $7. MKTTYPE $7. DMD PRICE VALUE;
datalines;
GGGGGG APR JJ RR AAACCC OTHOTH 4.072487387 5073.712404 1245.85098
GGGGGG APR LL RR AAACCC OTHOTH 0.759277544 527.855155 695.2071207
ROMANN APR SS RR ARNSSS TTTTTT 13.6940138 20710.54229 1512.379248
GGGGGG APR BB RR BAADDD OTHOTH 3.858234121 1971.730438 511.044788
BUHBUH APR QQ RR CAAHHH TTTTTT 670.3483179 607903.4554 906.8471407
GGGGGG APR KK RR DEEKKK TTTTTT 22.93870547 11473.72103 500.1904334
LIONNN APR UG RR EEEFFF OTHOTH 1.314052632 1776.681947 1352.063002
ROMANN APR IT RR DDMFCC OTHOTH 18.18630498 34948.34188 1921.684582
NOODLE APR AA RR MMMMUC TTTTTT 38.07123286 107524.5245 2824.298464
ROMANN APR IT WW BBRNNN OTHOTH 6.842336232 3142.693913 459.301298
NOODLE FEB PP RR GGGIII OTHOTH 1.240267277 3507.439478 2827.970665
BUHBUH JUL QQ RR AEEPPP UNDISC 0.610535011 282.9336958 463.419281
-----------------------------------------------------------------------------------------------
In the above data;
CNTRY is a branch of RGN.
But MKTs, MTHs, MKTTYPE can exist for all RGNs and CNTRYs.
VALUE = DMD*PRICE
I have some constant PRICE and VALUE targets at various levels:
- RGN level
- CNTRY level
- CLS level
And the overall VALUE and DMD should also meet MTH level targets.
For a start I created just some RGN level target using the below:
------------------------------------
proc sql;
create table TABLE_TGT as
select unique RGN,
sum(VALUE)*1.1 as VALUE_TGT format dollar16.0,
0.95*(sum(VALUE)/sum(DMD)) as PRICE_TGT format dollar16.0
from TABLE1
group by RGN;
quit;
-------------------------------------
My decision variables are DMD & PRICE.
PRICE would be constrained to +/- 10% change from the starting value.
The below full code gives errors
/************************************************************************/
--------------------- Full Code ------------------------------------------
data TABLE1;
input RGN $7. MTH $4. CNTRY $3. CLS $3. MKT $7. MKTTYPE $7. DMD PRICE VALUE;
datalines;
GGGGGG APR JJ RR AAACCC OTHOTH 4.072487387 5073.712404 1245.85098
GGGGGG APR LL RR AAACCC OTHOTH 0.759277544 527.855155 695.2071207
ROMANN APR SS RR ARNSSS TTTTTT 13.6940138 20710.54229 1512.379248
GGGGGG APR BB RR BAADDD OTHOTH 3.858234121 1971.730438 511.044788
BUHBUH APR QQ RR CAAHHH TTTTTT 670.3483179 607903.4554 906.8471407
GGGGGG APR KK RR DEEKKK TTTTTT 22.93870547 11473.72103 500.1904334
LIONNN APR UG RR EEEFFF OTHOTH 1.314052632 1776.681947 1352.063002
ROMANN APR IT RR DDMFCC OTHOTH 18.18630498 34948.34188 1921.684582
NOODLE APR AA RR MMMMUC TTTTTT 38.07123286 107524.5245 2824.298464
ROMANN APR IT WW BBRNNN OTHOTH 6.842336232 3142.693913 459.301298
NOODLE FEB PP RR GGGIII OTHOTH 1.240267277 3507.439478 2827.970665
BUHBUH JUL QQ RR AEEPPP UNDISC 0.610535011 282.9336958 463.419281
;
proc sql;
create table TABLE_RGN as
select unique RGN from TABLE1; quit;
proc sql;
create table TABLE_MN as
select unique MTH from TABLE1; quit;
proc sql;
create table TABLE_PS as
select unique CNTRY from TABLE1; quit;
proc sql;
create table TABLE_CM as
select unique CLS from TABLE1; quit;
proc sql;
create table TABLE_ND as
select unique MKT from TABLE1; quit;
proc sql;
create table TABLE_OT as
select unique MKTTYPE from TABLE1; quit;
/*****************************************************************************************/
/* Dummy data set only to provide constant targets */
proc sql;
create table TABLE_TGT as
select unique RGN,
sum(VALUE)*1.1 as VALUE_TGT format dollar16.0,
0.95*(sum(VALUE)/sum(DMD)) as PRICE_TGT format dollar16.0
from TABLE1
group by RGN;
quit;
/*****************************************************************************************/
options missing=0;
proc optmodel;
/* declare index sets and read values from data sets*/
set <str> RGN_I;
read data TABLE_RGN nomiss into RGN_I = [RGN];
set <str> MTH_I;
read data TABLE_MN nomiss into MTH_I = [MTH];
set <str> CNTRY_I;
read data TABLE_PS nomiss into CNTRY_I = [CNTRY];
set <str> CLS_I;
read data TABLE_CM nomiss into CLS_I = [CLS];
set <str> MKT_I;
read data TABLE_ND nomiss into MKT_I = [MKT];
set <str> MKTTYPE_I;
read data TABLE_OT nomiss into MKTTYPE_I = [MKTTYPE];
/* declare parameters and read from data sets */
num DMD {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};
read data TABLE1 nomiss into [RGN MTH CNTRY CLS MKT MKTTYPE] DMD;
num PRICE {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};
read data TABLE1 nomiss into [RGN MTH CNTRY CLS MKT MKTTYPE] PRICE;
num VALUE_TGT {RGN_I};
read data TABLE_TGT nomiss into RGN_I =[RGN] VALUE_TGT;
num PRICE_TGT {RGN_I};
read data TABLE_TGT nomiss into RGN_I =[RGN] PRICE_TGT;
/* test that the data is read*/
print VALUE_TGT PRICE_TGT;
print DMD PRICE;
/* declare variables */
var x {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};
var y {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};
impvar z {rc in RGN_I}
= sum{r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I}
x[r,m,p,c,k,t]*y[r,m,p,c,k,t];
/* define constraints */
con z_CON {rc in RGN_I}:
sum {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I}
x[r,m,p,c,k,t]*y[r,m,p,c,k,t] = VALUE_TGT[rc];
con y_CON {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I}:
y [r,m,p,c,k,t] = (if PRICE[r,m,p,c,k,t]=. then y [r,m,p,c,k,t]=0
else (y [r,m,p,c,k,t]>=PRICE[r,m,p,c,k,t]*0.9 and y [r,m,p,c,k,t]<=PRICE[r,m,p,c,k,t]*1.1));
/* Objective Function */
min MSE = sum{r in RGN_I}
(z
solve;
print z;
quit;
--------------------------------------------- Errors ----------------------------------------------------
125 con y_CON {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I}:
126 y [r,m,p,c,k,t] = (if PRICE[r,m,p,c,k,t]=. then y [r,m,p,c,k,t]=0
127 else (y [r,m,p,c,k,t]>=PRICE[r,m,p,c,k,t]*0.9 and y [r,m,p,c,k,t]<=PRICE[r,m,p,c,k,t]*1.1));
____
531
ERROR 531-782: The ELSE does not match an IF.
128
129 /* Objective Function */
130 min MSE = sum{r in RGN_I}
131 (z
132
133 solve;
NOTE: Problem generation will use 4 threads.
NOTE: Previous errors might cause the problem to be resolved incorrectly.
ERROR: The constraint 'y_CON' has an incomplete declaration.
NOTE: The problem has 19800 variables (19800 free, 0 fixed).
NOTE: The problem uses 5 implicit variables.
NOTE: The problem has 0 linear constraints (0 LE, 0 EQ, 0 GE, 0 range).
NOTE: The problem has 5 nonlinear constraints (0 LE, 5 EQ, 0 GE, 0 range).
NOTE: Unable to create problem instance due to previous errors.
----------------------------------------------- End --------------------------------------------------
/*****************************************************************************************************/
Here is one way to express the desired constraints by using two sets of constraints with logical conditions:
con y_CON1 {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] = .}:
y[r,m,p,c,k,t] = 0;
con y_CON2 {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] ne .}:
PRICE[r,m,p,c,k,t]*0.9 <= y[r,m,p,c,k,t] <= PRICE[r,m,p,c,k,t]*1.1;
And here is an alternative approach that uses one set of range constraints with two IF-THEN-ELSE expressions:
con y_CON {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I}:
(if PRICE[r,m,p,c,k,t] = . then 0 else PRICE[r,m,p,c,k,t]*0.9)
<= y[r,m,p,c,k,t]
<= (if PRICE[r,m,p,c,k,t] = . then 0 else PRICE[r,m,p,c,k,t]*1.1);
A third approach is to use the FIX statement when PRICE is missing:
for {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] = .}
fix y[r,m,p,c,k,t] = 0;
con y_CON {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] ne .}:
PRICE[r,m,p,c,k,t]*0.9 <= y[r,m,p,c,k,t] <= PRICE[r,m,p,c,k,t]*1.1;
What are you looking for ? and what output should be ?
require values for x & y that meet the constraints and for minimum MSE.
I am more interested in storing x & y in a data set (for their index values).
MSE should be pretty low.
I could do it in excel easily.
Here is one way to express the desired constraints by using two sets of constraints with logical conditions:
con y_CON1 {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] = .}:
y[r,m,p,c,k,t] = 0;
con y_CON2 {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] ne .}:
PRICE[r,m,p,c,k,t]*0.9 <= y[r,m,p,c,k,t] <= PRICE[r,m,p,c,k,t]*1.1;
And here is an alternative approach that uses one set of range constraints with two IF-THEN-ELSE expressions:
con y_CON {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I}:
(if PRICE[r,m,p,c,k,t] = . then 0 else PRICE[r,m,p,c,k,t]*0.9)
<= y[r,m,p,c,k,t]
<= (if PRICE[r,m,p,c,k,t] = . then 0 else PRICE[r,m,p,c,k,t]*1.1);
A third approach is to use the FIX statement when PRICE is missing:
for {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] = .}
fix y[r,m,p,c,k,t] = 0;
con y_CON {r in RGN_I, m in MTH_I, p in CNTRY_I, c in CLS_I, k in MKT_I, t in MKTTYPE_I: PRICE[r,m,p,c,k,t] ne .}:
PRICE[r,m,p,c,k,t]*0.9 <= y[r,m,p,c,k,t] <= PRICE[r,m,p,c,k,t]*1.1;
By the way, you also need to initialize PRICE to missing if you want to be able to check its value everywhere:
num PRICE {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I} init .;
Thanks Rob.
All 3 options worked. The "init ." did the trick.
SAS OR actually was prompting me for that.
I am now extending the model with more variables, more constraints and more data.
May need your help again.
This one is answered
Rgds
Praveen
Oh. It is IML question. Post it at SAS/IML Software and Matrix Computations
Dr Rick will help you . If use data step to get it ,that would be very messy and clumsy.
And what are constraints for x & y and how do you define MSE ?
Xia Keshan
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.