BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PraveenBala
Calcite | Level 5

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 - VALUE_TGT)**2;

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 - VALUE_TGT)**2;

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 --------------------------------------------------

/*****************************************************************************************************/

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

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;

View solution in original post

6 REPLIES 6
Ksharp
Super User

What are you looking for ? and what output should be ?

PraveenBala
Calcite | Level 5

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.

RobPratt
SAS Super FREQ

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;

RobPratt
SAS Super FREQ

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

PraveenBala
Calcite | Level 5

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 1254 views
  • 6 likes
  • 3 in conversation