Hi,
I am having some trouble using proc optmodel for the first time. I have the below table of 3 forecasts and the actual outturn for each month:
Month FC1 FC2 FC3 AC
1 111 125 120 118
2 208 151 183 172
3 157 166 155 154
.
.
.
12 202 215 199 209
What I am trying to find is which combination of the 3 forecasts would have given the minimal absolute error over the whole year, subject to 3 constraints. The trouble I am having with it is loading my observations in and summing over the whole period.
I have got it to work below for the first record by manually entering the values but any help would be appreciated. Also, is there a way to output the solution to a dataset rather than print? Thanks.
proc optmodel;
var w1, w2, w3;
minimize absErr = abs(((w1*111)+(w2*125)+(w3*120))-118);
con bound1: w1+w2+w3=1;
con bound2: max(w1,w2,w3)<= 1;
con bound3: min(w1,w2,w3)>= 0;
solve;
print w1 w2 w3;
quit;
The following should work for you:
/*Dataset with forecasts and actuals - expand as necessary*/
data inds;
input Month FC1 FC2 FC3 AC;
datalines;
1 111 125 120 118
2 208 151 183 172
3 157 166 155 154
4 202 215 199 209
run;
proc optmodel;
var w1, w2, w3;
/*create a set of month - the values are read from the input dataset and will adapt to the changes in that*/
set<num> MONTHS;
/*create number parameters running over the set of months*/
num FC1{MONTHS};
num FC2{MONTHS};
num FC3{MONTHS};
num AC{MONTHS};
/*Read the input data into the set and the parameters */
read data inds into MONTHS=[Month] FC1 FC2 FC3 AC;
print FC1;
/*summarize the error over each month and parameterize the forecasted and actual values*/
minimize absErr = sum{i in MONTHS} (abs(((w1*FC1[i])+(w2*FC2[i])+(w3*FC3[i]))-AC[i]));
con bound1: w1+w2+w3=1;
con bound2: max(w1,w2,w3)<= 1;
con bound3: min(w1,w2,w3)>= 0;
solve;
print w1 w2 w3;
/*create a simple output dataset with the value of the 3 variables*/
create data outds from w1 w2 w3;
quit;
The following should work for you:
/*Dataset with forecasts and actuals - expand as necessary*/
data inds;
input Month FC1 FC2 FC3 AC;
datalines;
1 111 125 120 118
2 208 151 183 172
3 157 166 155 154
4 202 215 199 209
run;
proc optmodel;
var w1, w2, w3;
/*create a set of month - the values are read from the input dataset and will adapt to the changes in that*/
set<num> MONTHS;
/*create number parameters running over the set of months*/
num FC1{MONTHS};
num FC2{MONTHS};
num FC3{MONTHS};
num AC{MONTHS};
/*Read the input data into the set and the parameters */
read data inds into MONTHS=[Month] FC1 FC2 FC3 AC;
print FC1;
/*summarize the error over each month and parameterize the forecasted and actual values*/
minimize absErr = sum{i in MONTHS} (abs(((w1*FC1[i])+(w2*FC2[i])+(w3*FC3[i]))-AC[i]));
con bound1: w1+w2+w3=1;
con bound2: max(w1,w2,w3)<= 1;
con bound3: min(w1,w2,w3)>= 0;
solve;
print w1 w2 w3;
/*create a simple output dataset with the value of the 3 variables*/
create data outds from w1 w2 w3;
quit;
Amazing! Thank you
Without linearizing your model, the solver is unlikely to find a globally optimal solution. You can avoid the nonlinear MAX and MIN functions by omitting the bound1 and bound2 constraints and instead enforcing these bounds in the VAR statement:
var w1 >= 0 <= 1, w2 >= 0 <= 1, w3 >= 0 <= 1;
You can also linearize the absolute value in the objective by introducing Surplus and Slack variables:
var Surplus {MONTHS} >= 0;
var Slack {MONTHS} >= 0;
con LinearizeAbs {i in MONTHS}:
w1*FC1[i] + w2*FC2[i] + w3*FC3[i] - AC[i] = Surplus[i] - Slack[i];
minimize absErr = sum{i in MONTHS} (Surplus[i] + Slack[i]);
See the Curve Fitting and Market Sharing examples in SAS/OR 14.3 User's Guide: Mathematical Programming Examples.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.