BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sanj5
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Berliner_Ørsted
Fluorite | Level 6

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;

 

View solution in original post

3 REPLIES 3
Berliner_Ørsted
Fluorite | Level 6

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
Calcite | Level 5

Amazing! Thank you

RobPratt
SAS Super FREQ

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.

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

Register now

Discussion stats
  • 3 replies
  • 1938 views
  • 0 likes
  • 3 in conversation