Forecasting using SAS Forecast Server, SAS/ETS, and more

How to output MSE / MAE information into an output dataset?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

How to output MSE / MAE information into an output dataset?

Hi,

 

I used 'Proc Autoreg' procedure to predict a time trend data and have got a question related to it. My code:

 

Proc Autoreg Data = Work.data1;

    Model Y = time / nlag = 3  method = ml  backstep;

    by A B C;

    output out = Work.Result_1  p = yhat   pm = trendhat;

Run;

 

Itr runs successfully, however, summary statistics shows in 'Result' page which is printed, how could I obtain its related MSE & MAE in an output dataset? Thank you! 

 

 


Accepted Solutions
Solution
‎05-17-2017 09:57 AM
SAS Employee
Posts: 26

Re: How to output MSE / MAE information into an output dataset?

Hi Chenxi,

 

You can use an ODS OUTPUT statement to output information displayed by a procedure to a SAS data set.  The following SAS Note provides more details on this:

 

SAS Note 22949

 

In PROC AUTOREG, the FitSummary ODS table contains the MSE and MAE statistics, therefore, you can add the following statement to your PROC AUTOREG step to output the FitSummary table to a SAS data set called fitstats:

 

ODS OUTPUT FitSummary=fitstats;

 

Note that PROC AUTOREG potentially creates 2 FitSummary tables--one for the OLS estimation and one for the Final Estimation.  Because your application uses the BACKSTEP option to remove nonsignificant AR terms from the model, it is possible that only the FitSummary table for the OLS estimation will be produced for some BY groups.  This occurs when all AR terms are removed from the model for a given BY group.  For BY groups that include at least one AR term in the model, both the OLS and Final estimation FitSummary results will be included in the fitstats data set.  If you omit the BACKSTEP option from your PROC AUTOREG step, then you could specify that only the final set of fit summary statistics be output using the following statement:

 

ODS OUTPUT FinalModel.FitSummary=fitstats;

 

Please try the following modification to your code to obtain your desired statistics:

 

Proc Autoreg Data = Work.data1;
    ods output FitSummary=fitstats;  /*  added statement */
    Model Y = time / nlag = 3  method = ml  backstep;
    by A B C;
    output out = Work.Result_1  p = yhat   pm = trendhat;
Run;

proc print data=fitstats;
run;

  /*  to subset the data to only include the MSE and MAE statistics */
data final;
  set fitstats(where=(Label1 in ('MSE','MAE')));
  rename label1=Stat 
         nvalue1=value;
  keep A B C label1 nvalue1;
run;

proc print data=final;
run;

 

 

I generally do not recommend the FORECAST procedure, but because your model is a linear time trend regression model with AR errors, the default STEPAR method provides another alternative for your application.  Although the results will not be identical to your above PROC AUTOREG step, they should be very close.  The OUTEST= data set contains the MSE and MAE values:

 

proc forecast data=work.data1 method=stepar nlags=3 trend=2 lead=0
   out=out outactual out1step outest=est;
   by A B C;
   var y;
run;

proc print data=est(where=(_type_ in ('MSE','MAE')));
run;

 

 

I hope this helps!

DW

View solution in original post


All Replies
Solution
‎05-17-2017 09:57 AM
SAS Employee
Posts: 26

Re: How to output MSE / MAE information into an output dataset?

Hi Chenxi,

 

You can use an ODS OUTPUT statement to output information displayed by a procedure to a SAS data set.  The following SAS Note provides more details on this:

 

SAS Note 22949

 

In PROC AUTOREG, the FitSummary ODS table contains the MSE and MAE statistics, therefore, you can add the following statement to your PROC AUTOREG step to output the FitSummary table to a SAS data set called fitstats:

 

ODS OUTPUT FitSummary=fitstats;

 

Note that PROC AUTOREG potentially creates 2 FitSummary tables--one for the OLS estimation and one for the Final Estimation.  Because your application uses the BACKSTEP option to remove nonsignificant AR terms from the model, it is possible that only the FitSummary table for the OLS estimation will be produced for some BY groups.  This occurs when all AR terms are removed from the model for a given BY group.  For BY groups that include at least one AR term in the model, both the OLS and Final estimation FitSummary results will be included in the fitstats data set.  If you omit the BACKSTEP option from your PROC AUTOREG step, then you could specify that only the final set of fit summary statistics be output using the following statement:

 

ODS OUTPUT FinalModel.FitSummary=fitstats;

 

Please try the following modification to your code to obtain your desired statistics:

 

Proc Autoreg Data = Work.data1;
    ods output FitSummary=fitstats;  /*  added statement */
    Model Y = time / nlag = 3  method = ml  backstep;
    by A B C;
    output out = Work.Result_1  p = yhat   pm = trendhat;
Run;

proc print data=fitstats;
run;

  /*  to subset the data to only include the MSE and MAE statistics */
data final;
  set fitstats(where=(Label1 in ('MSE','MAE')));
  rename label1=Stat 
         nvalue1=value;
  keep A B C label1 nvalue1;
run;

proc print data=final;
run;

 

 

I generally do not recommend the FORECAST procedure, but because your model is a linear time trend regression model with AR errors, the default STEPAR method provides another alternative for your application.  Although the results will not be identical to your above PROC AUTOREG step, they should be very close.  The OUTEST= data set contains the MSE and MAE values:

 

proc forecast data=work.data1 method=stepar nlags=3 trend=2 lead=0
   out=out outactual out1step outest=est;
   by A B C;
   var y;
run;

proc print data=est(where=(_type_ in ('MSE','MAE')));
run;

 

 

I hope this helps!

DW

Frequent Contributor
Posts: 107

Re: How to output MSE / MAE information into an output dataset?

[ Edited ]

Thank you DW, this is extremely helpful!

 

I removed 'Backstep', 

However, I looked at the 'FitStats' table, for each combination of 'A', 'B' & 'C', there are two 'MSE' &' MAE' for column 'Label1'. which one shall I use? Thank you! 

SAS Employee
Posts: 26

Re: How to output MSE / MAE information into an output dataset?

Hi Crubal,

 

I'm glad the information was helpful to you! 

 

If you omit the BACKSTEP option, then you will always get 2 sets of fit statistics when you output the FitSummary ODS table to a data set.  The first set of statistics correspond with the preliminary OLS estimation, and the second set corresponds with the final estimation.  If you only want the fit statistics for the final model to be output to a data set, then you can specify:

 

ods output FinalModel.FitSummary=fitstats;

 

I hope this helps!

DW

 

Frequent Contributor
Posts: 107

Re: How to output MSE / MAE information into an output dataset?

Thank you so much DW!
Frequent Contributor
Posts: 107

Re: How to output MSE / MAE information into an output dataset?

Hi DW,

 

I am sorry I have got additional question. 

 

After using 'Proc Autoreg' procedure under the prior code, and obtained 'yhat' for predicted value. 

 

As variable 'time' I set is used applied among passed dates. And this variable has date from '01MAY2013' to '30APR2018'. 

If I would like to apply the model in forecasting future dates, which is '01MAY2017' to '30APR2018', how could I write the code? Thank you so much! 

 

Initial Code: 

Proc Autoreg Data = Work.data1;
    ods output FitSummary=fitstats;  /*  added statement */
    Model Y = time / nlag = 3  method = ml  backstep;
    by A B C;
    output out = Work.Result_1  p = yhat   pm = trendhat;
where time between '01MAY2013'd and '30APR2017'd; Run; proc print data=fitstats; run;

 

SAS Employee
Posts: 26

Re: How to output MSE / MAE information into an output dataset?

 

Hi Crubal,

 

Thanks for your follow-up question.  I'm happy to help!

 

If:

  • your input data set has values of your TIME variable that range from '01MAY2013' through '30APR2018' within each BY group, and
  • you want the model within each BY group to be estimated using the range of data from '01MAY2013' through '30APR2017', and
  • you want predicted values for all dates (ie. '01MAY2013' through '30APR2018'),

then you should:

  • remove the WHERE statement from your PROC AUTOREG step, and
  • set Y to missing (.) for observations where TIME is greater than '30APR2017' within each BY group.

The model will only be estimated using those observations that have nonmissing values for Y and TIME, but predicted values will be computed for all observations in the DATA= data set that have a value for the input variable, TIME.

 

For more details on obtaining forecasts beyond the range of historical data using PROC AUTOREG, please see the following section of the documentation:

 

Forecasting Autoregressive Error Models

 

I hope this helps!

DW

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 270 views
  • 1 like
  • 2 in conversation