ARIMAX MODELS using PROC ARIMA - problems replicating forecasts produced in SAS, in excel

[alexfx]

Hello,

I am building several time series model for several target variables. I am using arimax models and using Proc Arima procedure.

The code i used for 3 example targets is the following:

Target1

ods graphics on;

proc arima data=amostra_des_2 plots=all;
	identify var=N_&var_nvl.(1) minic esacf scan crosscorr=(

_1dif_GDP_perc_yoy
_1dif_indice_prd_const_Var_Lag1Y
);
	
	estimate p=(0) q=(4) input=(

_1dif_GDP_perc_yoy
_1dif_indice_prd_const_Var_Lag1Y

) 
	printall Outest=modelos.Parametros_1NS2 outstat=modelos.Estatisticas_1NS2;
	forecast ID=date LEAD=8 INTERVAL=QTR ALPHA=0.05 align=ending OUT=modelos.previsoes_1NS2;
run;

Target2

ods graphics on;

proc arima data=amostra_des_2 plots=all;
	identify var=N_&var_nvl.(1,1) minic esacf scan crosscorr=(
_2dif_resid_prices_EUR_Var_Lag1Y
_2dif_TX_JR_consm_EUR_Var_Lag1Y
_2dif_GDP_perc_yoy
);
	estimate p=(4) q=(0) input=(
_2dif_resid_prices_EUR_Var_Lag1Y
_2dif_TX_JR_consm_EUR_Var_Lag1Y
_2dif_GDP_perc_yoy
) 

	printall Outest=modelos.Parametros_9NS3 outstat=modelos.Estatisticas_9NS3;
	forecast ID=date LEAD=8 INTERVAL=QTR ALPHA=0.05 align=ending OUT=modelos.previsoes_9NS3;
run;

Target3

ods graphics on;

proc arima data=amostra_des_2 plots=all;
	identify var=N_&var_nvl.(1,1) minic esacf scan crosscorr=(
_2dif_GDP_perc_yoy
);

	estimate p=(1) q=(4) input=(
_2dif_GDP_perc_yoy
)
	printall Outest=modelos.Parametros_11NS outstat=modelos.Estatisticas_11NS;
	forecast ID=date LEAD=8 INTERVAL=QTR ALPHA=0.05 align=ending OUT=modelos.previsoes_11NS;
run;

For the first target, the best fit turned out to be an ARIMAX(d=1, p=0, q= 4), for the second target it was an ARIMAX(d=2,p=4,q=0) and for the third target it was an ARIMAX(d=2,p=1,q=4).

For the first two models, in which there is only one componente (AR (p) term or MA (q) term) I was able to replicate the forecasts produced by "Proc arima" in excel, in order to better understand the final equation, but in the third case, in which there are two terms (both p (AR) and q (MA) are above 0) I wasn’t able to replicate the results in excel.

In attach you can find 3 excel files, one for each target, so you can better understand the calculations i've done in order to replicate the forecasts generated by the code.

Can you help me understand what I am doing wrong in my attempt of replication for target 3?

Thank you.

[sbxkoenk]

Hello @alexfx ,

W.r.t. implementing ARIMA(X) in Excel.

This question pops up regularly, also on SAS-internal forums.

I am a bit reluctant to copy/paste from these internal discussions (although very funny sometimes), but the key-message is:

DO NOT TRY TO DO THIS!

It's nearly impossible (de facto impossible).

As a colleague said: the cost of hiring a consultant to do it may break the bank.

Calculating ARIMA forecasts requires sophisticated polynomial algebra and matrix algebra functions, something (probably) not available in Excel without buying additional add-ins. Double precision floating point arithmetic is essential.

And you will also stumble upon this issue: Infinite Memory Forecasts versus Finite Memory Forecasts. See SAS documentation for this!

But why do you try to replicate in Excel??

Because some people would like to fiddle with the model and do what-if analyses?

You can call SAS forecasting procedures from Excel if you like (with Office Analytics).

Cheers,

Koen

[sbxkoenk]

Hello,

Go to the communities home page and type in the search field: replicating ARIMA in Excel

Make sure you're searching in "All community".

You will find several hits.

I haven't opened any of them but I guess these entries/topics will make the same statement as I'm doing above.

I agree that many business forecasts are generated by Excel out there, but this is something we want to fix, not something we want to exploit. 🤔😉.

Kind regards,

Koen

[alexfx]

Hi @sbxkoenk .
Thanks for your reply.
I was just trying to understand the final equation that equals to the final forecast series. I am not trying to replicate the entire process of forecasting that SAS does in Excel, just the final numbers.
Do you think this is something really complex? I was able to replicate the final forecast series for models with just AR terms or MA terms. But not able to do it for models with both AR and MA terms.

Thank you again.

[sbxkoenk]

Hello @alexfx ,

I know you just want to do forecasting in Excel with the equation(s) estimated by SAS.

I know you do not want to replicate the entire estimation process of SAS in Excel.

But even then, it's very complex.

Not so much for pure AR-models or pure MA-models, but for ARMA models it's definitely complex.

And for ARIMA and ARIMAX, I would not even try.

Do you really want to proceed with this?

I am not so handy in Excel, but I can maybe prepare something in SAS that allows you to replicate in Excel.

Only if you have ARMA ( and not ARIMA(X) ). 

Kind regards,

Koen

[alexfx]

Hi @sbxkoenk

Thank you so much for your help once again.

The models for which I was trying to do this are all ARIMAX. I will have to think of other options...

Thanks for your availability,
Best Regards