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 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Multiple Linear Regression in SAS

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.

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