Calcite | Level 5

## Excel Solver using Proc Optmodel

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!

SAS Super FREQ

## Re: Excel Solver using Proc Optmodel

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

Discussion stats