Find a combination using proc optmodel

[sanj5]

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;

[Berliner_Ørsted · Accepted solution]

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;

[sanj5]

Amazing! Thank you

[RobPratt]

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.