topic Re: Airline Rubix in Mathematical Optimization, Discrete-Event Simulation, and OR

topic Re: Airline Rubix in Mathematical Optimization, Discrete-Event Simulation, and OR https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153274#M794 <HTML><HEAD></HEAD><BODY><P>What are you looking for ? and what output should be ?</P></BODY></HTML> Wed, 17 Dec 2014 13:08:50 GMT Ksharp 2014-12-17T13:08:50Z Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153273#M793 <HTML><HEAD></HEAD><BODY><P>Hi All,</P><P>I have been solving an airline demand, price and value combination problem with Excel solver pro.</P><P>However the input size has grown beyond the limits of excel and therefore I am trying to solve it with SAS OR.</P><P>Below is only masked dummy data containing 12 rows. The actual data would have 500,000 rows.</P><P>Thanking you in anticipation.</P><P>Rgds</P><P>Praveen</P><P></P><P></P><P>Text file is attached for easy download and execution. n,  </P><P>---------------------------------------------------------------------------------------------------------------------</P><P></P><P>data TABLE1;</P><P>input RGN $7. MTH $4. CNTRY $3. CLS $3. MKT $7. MKTTYPE $7. DMD PRICE VALUE;</P><P>datalines;</P><P>GGGGGG APR JJ RR AAACCC OTHOTH 4.072487387 5073.712404 1245.85098</P><P>GGGGGG APR LL RR AAACCC OTHOTH 0.759277544 527.855155 695.2071207</P><P>ROMANN APR SS RR ARNSSS TTTTTT 13.6940138 20710.54229 1512.379248</P><P>GGGGGG APR BB RR BAADDD OTHOTH 3.858234121 1971.730438 511.044788</P><P>BUHBUH APR QQ RR CAAHHH TTTTTT 670.3483179 607903.4554 906.8471407</P><P>GGGGGG APR KK RR DEEKKK TTTTTT 22.93870547 11473.72103 500.1904334</P><P>LIONNN APR UG RR EEEFFF OTHOTH 1.314052632 1776.681947 1352.063002</P><P>ROMANN APR IT RR DDMFCC OTHOTH 18.18630498 34948.34188 1921.684582</P><P>NOODLE APR AA RR MMMMUC TTTTTT 38.07123286 107524.5245 2824.298464</P><P>ROMANN APR IT WW BBRNNN OTHOTH 6.842336232 3142.693913 459.301298</P><P>NOODLE FEB PP RR GGGIII OTHOTH 1.240267277 3507.439478 2827.970665</P><P>BUHBUH JUL QQ RR AEEPPP UNDISC 0.610535011 282.9336958 463.419281</P><P>-----------------------------------------------------------------------------------------------</P><P>In the above data; </P><P>CNTRY is a branch of RGN. </P><P>But MKTs, MTHs, MKTTYPE can exist for all RGNs and CNTRYs.</P><P>VALUE = DMD*PRICE</P><P></P><P>I have some constant PRICE and VALUE targets at various levels:</P><P>- RGN level</P><P>- CNTRY level</P><P>- CLS level</P><P>And the overall VALUE and DMD should also meet MTH level targets.</P><P></P><P></P><P>For a start I created just some RGN level target using the below:</P><P>------------------------------------</P><P>proc sql;</P><P>create table TABLE_TGT as</P><P>select unique RGN, </P><P>  sum(VALUE)*1.1 as VALUE_TGT format dollar16.0, </P><P>  0.95*(sum(VALUE)/sum(DMD)) as PRICE_TGT format dollar16.0</P><P>from TABLE1</P><P>group by RGN;</P><P>quit;</P><P>-------------------------------------</P><P>My decision variables are DMD & PRICE.</P><P>PRICE would be constrained to +/- 10% change from the starting value.</P><P></P><P>The below full code gives errors</P><P>/************************************************************************/</P><P>--------------------- Full Code ------------------------------------------</P><P>data TABLE1;</P><P>input RGN $7. MTH $4. CNTRY $3. CLS $3. MKT $7. MKTTYPE $7. DMD PRICE VALUE;</P><P>datalines;</P><P>GGGGGG APR JJ RR AAACCC OTHOTH 4.072487387 5073.712404 1245.85098</P><P>GGGGGG APR LL RR AAACCC OTHOTH 0.759277544 527.855155 695.2071207</P><P>ROMANN APR SS RR ARNSSS TTTTTT 13.6940138 20710.54229 1512.379248</P><P>GGGGGG APR BB RR BAADDD OTHOTH 3.858234121 1971.730438 511.044788</P><P>BUHBUH APR QQ RR CAAHHH TTTTTT 670.3483179 607903.4554 906.8471407</P><P>GGGGGG APR KK RR DEEKKK TTTTTT 22.93870547 11473.72103 500.1904334</P><P>LIONNN APR UG RR EEEFFF OTHOTH 1.314052632 1776.681947 1352.063002</P><P>ROMANN APR IT RR DDMFCC OTHOTH 18.18630498 34948.34188 1921.684582</P><P>NOODLE APR AA RR MMMMUC TTTTTT 38.07123286 107524.5245 2824.298464</P><P>ROMANN APR IT WW BBRNNN OTHOTH 6.842336232 3142.693913 459.301298</P><P>NOODLE FEB PP RR GGGIII OTHOTH 1.240267277 3507.439478 2827.970665</P><P>BUHBUH JUL QQ RR AEEPPP UNDISC 0.610535011 282.9336958 463.419281</P><P>;</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_RGN as</P><P>select unique RGN from TABLE1; quit;</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_MN as</P><P>select unique MTH from TABLE1; quit;</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_PS as</P><P>select unique CNTRY from TABLE1; quit;</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_CM as</P><P>select unique CLS from TABLE1; quit;</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_ND as</P><P>select unique MKT from TABLE1; quit;</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_OT as</P><P>select unique MKTTYPE from TABLE1; quit;</P><P></P><P></P><P>/*****************************************************************************************/</P><P>/* Dummy data set only to provide constant targets */</P><P></P><P></P><P>proc sql;</P><P>create table TABLE_TGT as</P><P>select unique RGN, </P><P>  sum(VALUE)*1.1 as VALUE_TGT format dollar16.0, </P><P>  0.95*(sum(VALUE)/sum(DMD)) as PRICE_TGT format dollar16.0</P><P>from TABLE1</P><P>group by RGN;</P><P>quit;</P><P>/*****************************************************************************************/</P><P></P><P></P><P>options missing=0;</P><P>proc optmodel;</P><P></P><P></P><P>/* declare index sets and read values from data sets*/</P><P></P><P></P><P>set <str> RGN_I;</P><P>read data TABLE_RGN nomiss into RGN_I = [RGN];</P><P></P><P></P><P>set <str> MTH_I;</P><P>read data TABLE_MN nomiss into MTH_I = [MTH];</P><P></P><P></P><P>set <str> CNTRY_I;</P><P>read data TABLE_PS nomiss into CNTRY_I = [CNTRY];</P><P></P><P></P><P>set <str> CLS_I;</P><P>read data TABLE_CM nomiss into CLS_I = [CLS];</P><P></P><P></P><P>set <str> MKT_I;</P><P>read data TABLE_ND nomiss into MKT_I = [MKT];</P><P></P><P></P><P>set <str> MKTTYPE_I;</P><P>read data TABLE_OT nomiss into MKTTYPE_I = [MKTTYPE];</P><P></P><P></P><P>/* declare parameters and read from data sets */</P><P></P><P></P><P>num DMD {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};</P><P>read data TABLE1 nomiss into [RGN MTH CNTRY CLS MKT MKTTYPE] DMD;</P><P></P><P></P><P>num PRICE {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};</P><P>read data TABLE1 nomiss into [RGN MTH CNTRY CLS MKT MKTTYPE] PRICE;</P><P></P><P></P><P>num VALUE_TGT {RGN_I};</P><P>read data TABLE_TGT nomiss into RGN_I =[RGN] VALUE_TGT;</P><P></P><P></P><P>num PRICE_TGT {RGN_I};</P><P>read data TABLE_TGT nomiss into RGN_I =[RGN] PRICE_TGT;</P><P></P><P></P><P>/* test that the data is read*/</P><P>print VALUE_TGT PRICE_TGT;</P><P>print DMD PRICE; </P><P></P><P>/* declare variables */</P><P>var x {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};</P><P>var y {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I};</P><P>impvar z {rc in RGN_I} </P><P>= 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} </P><P>x[r,m,p,c,k,t]*y[r,m,p,c,k,t];</P><P></P><P></P><P>/* define constraints */</P><P>con z_CON {rc in RGN_I}: </P><P>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}</P><P>x[r,m,p,c,k,t]*y[r,m,p,c,k,t] = VALUE_TGT[rc];</P><P></P><P></P><P>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}: </P><P>y [r,m,p,c,k,t] = (if PRICE[r,m,p,c,k,t]=. then y [r,m,p,c,k,t]=0 </P><P>  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));</P><P></P><P></P><P>/* Objective Function */</P><P>min MSE = sum{r in RGN_I}</P><P>  (z<R> - VALUE_TGT<R>)**2;</R></R></P><P></P><P></P><P>solve;</P><P></P><P></P><P>print z;</P><P></P><P></P><P>quit;</P><P></P><P></P><P>--------------------------------------------- Errors ----------------------------------------------------</P><P></P><P></P><P>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}:</P><P>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</P><P>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));</P><P>                ____</P><P>                531</P><P>ERROR 531-782: The ELSE does not match an IF.</P><P></P><P></P><P>128        </P><P>129        /* Objective Function */</P><P>130        min MSE = sum{r in RGN_I}</P><P>131         (z<R> - VALUE_TGT<R>)**2;</R></R></P><P>132        </P><P>133        solve;</P><P>NOTE: Problem generation will use 4 threads.</P><P>NOTE: Previous errors might cause the problem to be resolved incorrectly.</P><P>ERROR: <SPAN style="color: #ff0000;">The constraint 'y_CON' has an incomplete declaration.</SPAN></P><P>NOTE: The problem has 19800 variables (19800 free, 0 fixed).</P><P>NOTE: The problem uses 5 implicit variables.</P><P>NOTE: The problem has 0 linear constraints (0 LE, 0 EQ, 0 GE, 0 range).</P><P>NOTE: The problem has 5 nonlinear constraints (0 LE, 5 EQ, 0 GE, 0 range).</P><P>NOTE: Unable to create problem instance due to previous errors.</P><P></P><P></P><P>----------------------------------------------- End --------------------------------------------------</P><P>/*****************************************************************************************************/</P></BODY></HTML> Wed, 17 Dec 2014 08:41:14 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153273#M793 PraveenBala 2014-12-17T08:41:14Z Re: Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153274#M794 <HTML><HEAD></HEAD><BODY><P>What are you looking for ? and what output should be ?</P></BODY></HTML> Wed, 17 Dec 2014 13:08:50 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153274#M794 Ksharp 2014-12-17T13:08:50Z Re: Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153275#M795 <HTML><HEAD></HEAD><BODY><P>require values for x & y that meet the constraints and for minimum MSE.</P><P>I am more interested in storing x & y in a data set (for their index values).</P><P>MSE should be pretty low.</P><P>I could do it in excel easily.</P></BODY></HTML> Wed, 17 Dec 2014 13:15:25 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153275#M795 PraveenBala 2014-12-17T13:15:25Z Re: Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153276#M796 <HTML><HEAD></HEAD><BODY><P>Here is one way to express the desired constraints by using two sets of constraints with logical conditions: </P><P></P><P>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] = .}:<BR />   y[r,m,p,c,k,t] = 0;</P><P>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 .}:<BR />   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;</P><P></P><P>And here is an alternative approach that uses one set of range constraints with two IF-THEN-ELSE expressions: </P><P></P><P>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}:<BR />   (if PRICE[r,m,p,c,k,t] = . then 0 else PRICE[r,m,p,c,k,t]*0.9)<BR /><= y[r,m,p,c,k,t]<BR /><= (if PRICE[r,m,p,c,k,t] = . then 0 else PRICE[r,m,p,c,k,t]*1.1);</P><P></P><P>A third approach is to use the FIX statement when PRICE is missing: </P><P></P><P>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] = .}<BR />   fix y[r,m,p,c,k,t] = 0;</P><P>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 .}:<BR />   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;</P></BODY></HTML> Wed, 17 Dec 2014 14:58:16 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153276#M796 RobPratt 2014-12-17T14:58:16Z Re: Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153277#M797 <HTML><HEAD></HEAD><BODY><P>Oh. It is IML question. Post it at <A _jive_internal="true" data-containerid="2007" data-containertype="14" data-objectid="47" data-objecttype="14" href="https://communities.sas.com/community/support-communities/sas_iml_and_sas_iml_studio" style="font-size: 12.727272033691406px; color: #0e66ba; background-color: #f0f8fb;">SAS/IML Software and Matrix Computations</A></P><P>Dr Rick will help you . If use data step to get it ,that would be very messy and clumsy.</P><P></P><P><SPAN style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 12.727272033691406px; background-color: #ffffff;">And  what are constraints for <SPAN style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 12.727272033691406px; background-color: #ffffff;"> x & y</SPAN>  and how do you define MSE  ?</SPAN></P><P></P><P></P><P>Xia Keshan</P></BODY></HTML> Wed, 17 Dec 2014 16:00:30 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153277#M797 Ksharp 2014-12-17T16:00:30Z Re: Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153278#M798 <HTML><HEAD></HEAD><BODY><P>By the way, you also need to initialize PRICE to missing if you want to be able to check its value everywhere:</P><P>num PRICE {RGN_I, MTH_I, CNTRY_I, CLS_I, MKT_I, MKTTYPE_I} init .;</P></BODY></HTML> Wed, 17 Dec 2014 16:32:41 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153278#M798 RobPratt 2014-12-17T16:32:41Z Re: Airline Rubix https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153279#M799 <HTML><HEAD></HEAD><BODY><P>Thanks Rob.</P><P>All 3 options worked. The "init ." did the trick.</P><P>SAS OR actually was prompting me for that.</P><P></P><P>I am now extending the model with more variables, more constraints and more data.</P><P>May need your help again.</P><P></P><P>This one is answered</P><P></P><P>Rgds</P><P>Praveen </P></BODY></HTML> Sun, 21 Dec 2014 12:57:17 GMT https://communities.sas.com/t5/Mathematical-Optimization/Airline-Rubix/m-p/153279#M799 PraveenBala 2014-12-21T12:57:17Z