topic Re: PROC ESM and Backcasting in SAS Forecasting and Econometrics

PROC ESM and Backcasting

Dwayne — Thu, 04 Apr 2019 15:00:01 GMT

I’m currently using PROC ESM for exponential smoothing modeling using Holt-Winters “Additive” type methods for time series data sets with (and without) trend and with (and without) seasonality.

Question: Is there an ESM option (or another procedure) that will print out the “back-casting” details in a similar manner as with using the ESM’s “print=forecast” option?

For no-trend (model=simple) and no-trend w/seasonality (model=addseasonal), my EXCEL calculations always match the back-casting “state” values that I get from my SAS program. However, when I add a trend component to the model, my “final” back-casting state values no longer match – even assuming the same alpha, gamma and delta parameters, and starting values as SAS. I’ve already read the “Smoothing State Initialization” documentation.

My Code:

proc esm data=a print=(estimates forecasts performance statistics states) printdetails
lead=1
out=out_a outest=outest_a outfor=outfor_a outstat=outstat_a;
forecast customers / model=linear;
title3 'ESM Procedure';
run;

State Level Trend

Backcast 4,021 185.963235

Starting 810.20902 185.832622

Final 3,990.30259 185.849896

Using the above example for reference, what I need to see is SAS’s calculation flow from the initial Backcast state (4021/185.963235) to the “starting” forward’s modeling values at t=0 (810.20902/185.832622).

Thanks in advance!

Re: PROC ESM and Backcasting

dw_sas — Tue, 14 May 2019 15:39:24 GMT

Hi Dwayne,

PROC ESM does not provide a way to output the backcast values used to obtain the Starting Level and Trend values for the Linear exponential smoothing model, however, the smoothing calculations for the backcasts are essentially the same as the smoothing calculations for the "forwards casting". These smoothing calculations are described in the following section of the SAS/ETS User's Guide:

Equations for the Smoothing Models:

https://go.documentation.sas.com/?docsetId=etsug&docsetTarget=etsug_tffordet_sect021.htm&docsetVersion=15.1&locale=en

Please note, however, that for backcasting, the sign of the initial trend parameter is reversed, since the series is sorted in reverse order. Following, please find a description of the methodology used to compute the backcasts for MODEL=LINEAR in PROC ESM:

1) Add a time trend variable to the original data set, for example, "t", which is simply the observation number 1, 2, ..., n.

2) Obtain the initial Level and Slope values:

Initial Level is the value of the last observation in the data set
Initial Slope is the negative of the slope coefficient from regressing the time series variable on a time trend variable (ie. 1, 2,...n)

3) Sort the data, descending, by the time trend variable.

4) Backcast using the reverse-ordered data:

level [1] is: alpha*y[1] + (1-alpha)*(initial level + initial trend)
trend [1] is: gamma*(level[1] - initial level) + (1-gamma)*initial trend

5) Continue with smoothing computations through reverse ordered series using the equations noted in the above link.

6) To obtain the Starting Level and Trend values from the backcasts, go to the last observation from step 5)

Starting_level = Level[n] + Trend[n] (from step 5)
Starting_trend = -Trend[n] (from step 5);

I hope this description helps, Dwayne!

Re: PROC ESM and Backcasting

pipaso — Wed, 12 May 2021 10:04:48 GMT

Could you please write down the steps for backcasting with Holt-Winters Additive model? I could not figure out how to update seasonal index values.

Re: PROC ESM and Backcasting

Dwayne — Thu, 13 May 2021 01:02:27 GMT

To create the initial seasonal index values:

1.      Run a simple regression model with (k-1) dummyvariables. For example, if you need to create quarterly index values, you wouldhave three dummy variables. The resulting model will generate S1, S2, S3parameters that represent the initial unadjusted seasonal values. (unadjusted).

2.      To “create” the value for the kth index (i.e.,the forth season)

a.      average S1, S2, S3 to create S/mean)

b.      the final S4* (adjusted) is = 0 – S/mean)

3.      The unadjusted seasonal values from step 1 nowneed to be adjusted: SK *= Sk + S/mean

4.      If you want to create a “double check” the interceptvalue will also need to be adjusted: B0 + S/mean. The OLS forecastwith the raw unadjusted parameters (S1, S2, S3 and B0) should matchthat created by using the adjusted parameters (S1*, S2*, S3*, S4* and B*0).I always do this as a double check. NOTE: The adjusted values should equalthose indicated by PROC ESM under the “Smoothed States”: Backcast.
Note: the following example has a trend component in addition to seasonality.
Step 1: B0 = 85.36250

                B1= 1.78875    (trend component)

                S1= .44125       (unadjusted season-1)

                S2= -15.89750 (unadjusted season-2)

                S3= -42.41125 (unadjusted season-3)

Step 2: S*4 = 0 – average (.44125,-15.89750,-42.41125)= 0 – (-14.4669) = 14.4669 (adjusted season-4)

Step 3: S*1 = .44125 + 14.4669 = 14.90815          (adjusted season-1)

             S*2= -15.89750 + 14.4669 = -1.4306       (adjusted season-2)

             S*3= -42.41125 + 14.4669 = -27.94435  (adjusted season-3)

B*0= 85.36250 – 14.4669 = 70.8956)      (adjustedintercept is needed only if you want to double check in EXCEL).

B*1= B1 * (-1) Required only if your time series has trend and you are usingEXCEL to double check the backcasting and you will need to reverse this (* +1)before proceeding with the forward casting.

Note: the sum of the adjusted seasonal index valuesshould be zero!

Re: PROC ESM and Backcasting

pipaso — Thu, 13 May 2021 12:26:06 GMT

Thank you for the quick response. I tried what you said, but the sum of the adjusted seasonal index values is not zero. Here is my time series:

z	t	s1	s2	s3
17754	1	1	0	0
18220	2	0	1	0
18223	3	0	0	1
18047	4	0	0	0
19030	5	1	0	0
19122	6	0	1	0
18921	7	0	0	1
19023	8	0	0	0
19147	9	1	0	0
19397	10	0	1	0
19405	11	0	0	1
19605	12	0	0	0
20181	13	1	0	0
20067	14	0	1	0
20708	15	0	0	1
20476	16	0	0	0
20247	17	1	0	0
20351	18	0	1	0
21393	19	0	0	1
21590	20	0	0	0
21236	21	1	0	0

The regression results are:

	Coeffients
b0	17669,01053
b1	173,2657895
s1	24,23245614
s2	29,73157895
s3	155,0657895

Adjusted seasonal index values:

unadjusted	adjusted
24,23245614	93,90906433	s1
29,73157895	99,40818713	s2
155,0657895	224,7423977	s3
	-69,67660819	s4
mean
69,67660819

Am I doing something wrong?

Re: PROC ESM and Backcasting

Dwayne — Thu, 13 May 2021 23:49:27 GMT

Sorry about that. While the final values from my examplewere correct, the formulas could have been better spelled out.

2.      To “create” the value for the kth index (i.e.,the forth season)

a.      Sum(average S1, S2, S3)/k to create S/mean

b.      the final S4* (adjusted) is = 0 – S/mean)

3.      The unadjusted seasonal values from step1 now need to be adjusted: SK *= Sk - S/mean

In re-creating your time series, I calculated the followingresults:

Step 1: B0 = 17669.01

                B1= 173.2658    (trend component)

                S1= 24.23246    (unadjusted season-1)

                S2= 29.73158   (unadjusted season-2)

                S3= 155.06579 (unadjusted season-3)

Step 2: S/mean = (sum24.23246, 29.73158, 155.06579/4 = 52.2575

               S*4= 0 – (52.2575) = -52.2575 (adjusted season-4)

Step 3: S*1 = 24.23246 – ( 52.2575) = -28.0250     (adjusted season-1)

             S*2= 29.73158 – ( 52.2575) = -22.5259      (adjusted season-2)

             S*3= 155.06579 – ( 52.2575) = -102.8083 (adjusted season-3)

             B*0= 17669.01 +( 52.2575) = 17721.268    (adjusted intercept is needed only if youwant to double check in EXCEL).

Verify: sum (-28.0250,-22.5259, 102.8083,-52.2575) = 0

Whether you use the regression model’s coefficients or theadjusted seasonal index values, your predicted y’s (obs:1-4) should be 17866.51,18045.27, 18343.87, 18362.07

Again, I probably copied the “+/-“ sign and the “/4”incorrectly. Sorry about that. Let me know if this answered your question.

Re: PROC ESM and Backcasting

pipaso — Fri, 14 May 2021 03:17:39 GMT

Thanks again. Now I have my backcasting results (attached as an excel file) and need the initial values for Level, Trend, and Seasonal Index Values in the forecasting step. I know that I will use the original order of my time series. What should the initial values be regarding to the backcasting results?

Re: PROC ESM and Backcasting

Dwayne — Sat, 15 May 2021 02:51:06 GMT

Sorry for the delayed response, but I wanted to plug yourtime series into both SAS’s PROC ESM and my EXCEL spreadsheet to confirm.

Backcasting “STATE=BACKCAST”

Seasonal values are (Note that these values MUST added upto zero):

S*1 = -28.0250     (adjustedseason-1)

S*2 = -22.5259     (adjusted season-2)

S*3 = -102.8083   (adjusted season-3)

S*4 = -52.2575       (adjusted season-4)

Trend: Since you are (in effect) forecasting from period21 to period 1, the trend (B1) calculated from the regression model MUSTbe multiplied by -1 (i.e., it is now -173.2658).

Constant: This is simply the actual value for period 21(i.e., 21,236).

Weights

SAS calculates the following:

Level Weight = .07211

Trend Weight = .001

Seasonal Weight = .001

If you use EXCEL’s “SOLVER” utility you may get a slightlydifferent set of values.

Post Backcasting (“STATE=STARTING”)

Seasonal Values:

After the backcasting is completed, the seasonal values willno longer sum to ZERO. Before they can be used in the “forward casting”, youwill need to adjust them (yet again).

>From My EXECL calculations:

S1 = -27.13818

S2 = -21.86446

S3 = 103.519

S4 = -51.49149

This adds up to 3.024881. This MUST be ZERO’d out beforeyou can proceed. To do this:

S*1 = -27.13818 – (3.024881/4) = -27.8944

S*2 = -21.86446 – (3.024881/4) = -22.62068

S*3 = 103.519 – (3.024881/4) = 102.76278

S*4 = -51.49149 – (3.024881/4) = -52.2477

Trend: Since you are now projecting “forwards” the trendcalculated by the backcasting MUST be multiplied by -1 (i.e., -173.031 * -1 =173.031)

Constant: The final constant calculated by thebackcasting must be adjusted in the same manner as the seasonal values (exceptthat we now ADD 3.024881/4:

Constant * = 17662.410 + (3.024881/4) = 17,663.16622

“STATE=FINAL”

In EXCEL you should wind up with the following constant termand set of seasonal values:

Constant = 21,347.760

Trend = 173.081105

S1 = -27.70850

S2 = -22.45757

S3 = 102.91754

S4 = -52.10312

Lead=4:

Y22 = 21,347.760 + 173.081105(1) + (-22.45757)= 21,498.38354

Y23 = 21,347.760 + 173.081105(2) + (102.91754)= 21,796.83975

Y24 = 21,347.760 + 173.081105(3) + (-52.10312)= 21,814.9002

Y25 = 21,347.760 + 173.081105(4) + (-27.70850)= 22,012.3738

SAS CODE

  *************************

   * AdditiveHolt-Winters *

  *************************;

proc esm data=a print=(estimates forecasts performancestatistics states) printdetails

          seasonality=4 lead=4

          out=out_a outest=outest_a outfor=outfor_a outstat=outstat_a;

id periodinterval=qtr;

forecast sales /model=addwinters;

title3 'ESM Procedure';

run;

Hope this helps.

Re: PROC ESM and Backcasting

pipaso — Sat, 15 May 2021 04:42:34 GMT

Thanks again, but why is that your backcasting results are different than mine? Were there any mistakes in my excel file? You say the following, but how did you get those values?

From My EXECL calculations:
S1 = -27.13818
S2 = -21.86446
S3 = 103.519
S4 = -51.49149

Even if I use the weights you mention, I cannot get those results. My trend value after backcasting is also different than yours (Mine is 173.248441693523, yours is 173.081). Am I missing some point? Could you explain it by looking at backcasting results?

Re: PROC ESM and Backcasting

Dwayne — Sat, 15 May 2021 23:38:35 GMT

Question: Did you create a SAS program / PROC ESM to double check your calculations? If not, I highly recommend that you do so. While SAS doesn’t provide a detailed running calculation summary for it’s backcasting, it does do so for the “foreward casting” portion of the analysis. You can use that to double check that your EXCEL formulas are correct, since the only difference is that the trend must be multiplied by -1 and – instead – of calculating things for observations 21 to 1, calculations are given for observations 1 to 21.

I have attached the SAS output.

BACKCASTING (Partial EXCEL)

						ꚙ (constant)	ϒ (trend)		ꝺ (seasonal)		❺-❶
						.072110	.001000		.001000

Year	Time Period	Time Index	Actual #Disks Sold (y)	Projected #Disks Sold (ŷ)	et	Model Constant (at T)	Trend (at T)	Seasonal Time Index	Seasonal Index (Additive)	Model Constant - Trend (at T+1)	Projected Sales (ŷ)T+1
								1	-28.025000
								4	-52.257456
								3	102.808333
						21236.000	-173.26579	2	-22.525877	21062.734	21034.709
2021	I	21	21236	21034.709	201.29079	21077.249	-173.251	1	-27.83822	20903.998	20851.741
2020	IV	20	21590	20851.741	738.25944	20957.234	-173.198	4	-51.57243	20784.036	20886.844
2020	III	19	21393	20886.844	506.15580	20820.535	-173.162	3	103.27799	20647.373	20624.847
2020	II	18	20351	20624.847	-273.84734	20627.626	-173.181	2	-22.77998	20454.445	20426.607

......

2016	IV	4	18047	18312.625	-265.62512	18344.716	-173.041	4	-51.49149	18171.675	18275.243
2016	III	3	18223	18275.243	-52.24284	18167.908	-173.044	3	103.51900	17994.864	17972.770
2016	II	2	18220	17972.770	247.23005	18012.692	-173.026	2	-21.86446	17839.665	17812.581
2016	I	1	17754	17812.581	-58.58127	17835.441	-173.031	1	-27.13818	17662.410	17610.919
									Re-Normalization	3.024881

FORECASTING (PARTIAL EXCEL)

(note the sign reversal in the trend and the required adjustment in the seasonal values)

Year	Time Period	Time Index	Actual Hits (y)	Projected Hits (ŷ)	et	Model Constant (at T)	Trend (at T)	Seasonal Time Index	Seasonal Index (Additive)	Model Constant + Trend (at T+1)	Projected Hits (ŷ)T+1
				-27.8944				1	-27.894400
				-22.62068				2	-22.620700
				102.76278				3	102.762800
				-52.24771		17663.159	173.0307	4	-52.247700	17836.190	17808.296
2016	I	1	17754	17808.296	-54.296	17832.275	173.0268	1	-27.94478	18005.301	17982.681
2016	II	2	18220	17982.681	237.319	18022.415	173.0439	2	-22.40049	18195.458	18298.221
2016	III	3	18223	18298.221	-75.221	18190.034	173.0385	3	102.69300	18363.073	18310.825
2016	IV	4	18047	18310.825	-263.825	18344.048	173.0195	4	-52.49250	18517.068	18489.123

........

2020	II	18	20351	20783.704	-432.704	20774.558	173.0271	2	-22.45757	20947.585	21050.184
2020	III	19	21393	21050.184	342.816	20972.305	173.0518	3	102.91754	21145.357	21092.792
2020	IV	20	21590	21092.792	497.208	21181.210	173.0876	4	-52.10312	21354.298	21326.674
2021	I	21	21236	21326.674	-90.674	21347.760	173.0811	1	-27.70850	21520.841	21498.383

Re: PROC ESM and Backcasting

pipaso — Sun, 16 May 2021 05:45:16 GMT

After your detailed message, I noticed that I made 2 big mistakes. They can be seen from the excel file I uploaded in my previous message.

1) The order of the seasonal index values was wrong.

2) The formula for Model Constant was wrong.

Now, I have the same results with you. Thanks a lot.

Could you please suggest me a source about this subject? Do you figure out the formulas just by looking at the SAS results? Do you follow a source to solve the problem?

Thanks again. I am sorry that I have taken lots of your time.