Excel Solver using Proc Optmodel

MC29 — Mon, 09 Jul 2018 09:05:01 GMT

I am wondering how to replicate what I currently do in Excel using Solver, in SAS using Proc Optmodel. The example with sample data is attached. There are a number of columns with formulas that reference previous columns and also rows within the same column. The final derived value/column is then compared against an actual value/column to come up with the difference/error, which I would like to minimise by changing 3 other values used in the cell formulas. Would greatly appreciate your help on this. Thank you in advance!

Re: Excel Solver using Proc Optmodel

RobPratt — Mon, 09 Jul 2018 15:54:42 GMT

Here's one way:

proc optmodel;
   set OBS;
   num actual {OBS};
   read data indata into OBS=[_N_] actual;
   num L5 = (sum {i in 1..5} actual[i]) / 5;
   num T5 = 0;
   set OBS6 = {i in OBS: i >= 6};

   var Alpha, Beta, Gamma;
   var L {OBS6} init L5;
   var T {OBS6} init 0;
   var S {OBS} init 1;
   con Lcon {i in OBS6}: L[i] = (
      if i = 6 then Alpha*actual[i]/S[i-5]+(1-Alpha)*L5 
      else Alpha*actual[i]/S[i-5]+(1-Alpha)*(L[i-1]+T[i-1])
   );
   con Tcon {i in OBS6}: T[i] = (
      if i = 6 then Beta*(L[i]-L5)+(1-Beta)*T5
      else Beta*(L[i]-L[i-1])+(1-Beta)*T[i-1]
   );
   con Scon {i in OBS}: S[i] = (
      if i <= 5 then actual[i]/L5
      else Gamma*actual[i]/L[i]+(1-Gamma)*S[i-5]
   );
   impvar Forecast {i in OBS6} = (
      if i = 6 then L5*S[i-5]
      else (L[i-1]+T[i-1])*S[i-5]
   );
   impvar Error {i in OBS6} = Forecast[i] - actual[i];
   min SSE = sum {i in 6..50} Error[i]^2;

   solve with nlp / ms;
   print Alpha Beta Gamma;
   print actual L T S Forecast Error;
quit;

The resulting optimal solution yields an objective value of 558543.93638, with:

Alpha	Beta	Gamma
0.17297	-0.039394	0.13006

topic Excel Solver using Proc Optmodel in Mathematical Optimization, Discrete-Event Simulation, and OR

Excel Solver using Proc Optmodel

Re: Excel Solver using Proc Optmodel