YoY Growth Rate with Lag

[sas_user_1001]

Hi All - I am trying to (as efficiently as possible) create a table of year-over year forecasted values using a set of growth rates. My data appears as follows below. All of the data from 2020 to 2021 is given.

Starting in January of 2022 is where my forecast begins. For year 2022, month 1, my forecast, for each observation (e.g. A_Z1, A_Z2, etc.), should be equal to its respective value in year 2021, month 1 (i.e. a lag of 12 months) multiplied by (1 + growth). So, for instance, the first forecasted value of A_Z1 would be: lag12(A_Z1)*(1 + growth).

For year 2022, month 2, my forecast, for each observation should be equal to the value in year 2021, month 2 multiplied by (1 + growth). Rinse and repeat until the end of the forecast period. Any suggestions on how to most efficiently write this in SAS is much appreciated. Thanks.

year	month	A_Z1	A_Z2	A_Z3	A_Z4	A_total	B_Z1	B_Z2	B_Z3	B_Z4	B_total	C_Z1	C_Z2	C_Z3	C_Z4	C_total	growth
2020	1	6,020	9,106	5,898	3,143	9,753	9,878	5,829	3,426	7,322	4,974	2,785	1,287	5,564	7,965	4,527	.
2020	2	3,007	2,838	4,892	794	5,124	7,266	6,011	1,736	1,047	9,190	8,763	4,317	6,774	8,262	4,680	.
2020	3	6,658	3,809	2,590	2,783	806	4,792	1,005	4,374	2,372	5,845	2,408	6,940	4,164	9,576	2,398	.
2020	4	8,492	3,414	6,853	1,958	7,484	4,441	7,090	3,733	8,133	4,804	1,637	1,629	2,594	3,117	6,513	.
2020	5	8,503	5,162	4,947	6,870	5,892	3,820	3,062	6,356	1,531	3,749	1,233	6,340	2,595	4,242	971	.
2020	6	6,342	3,966	9,375	2,473	3,348	5,440	1,196	7,345	3,584	5,235	5,040	6,508	8,238	985	5,715	.
2020	7	515	2,878	3,718	1,572	4,348	5,896	8,399	9,296	2,552	2,415	3,252	759	3,608	822	7,619	.
2020	8	8,541	3,038	6,756	5,738	3,288	9,380	995	8,756	9,426	6,289	9,695	9,450	9,611	3,742	6,618	.
2020	9	8,010	7,618	8,719	3,751	1,516	7,122	3,727	3,519	9,199	4,323	6,757	8,114	6,945	7,839	1,703	.
2020	10	3,013	1,697	8,992	4,358	4,013	1,977	7,395	8,515	751	5,401	5,522	5,786	2,659	7,578	3,327	.
2020	11	5,344	6,324	8,052	2,488	5,327	4,439	8,716	4,614	1,323	2,754	4,496	6,311	1,823	3,611	7,636	.
2020	12	8,277	7,321	2,051	7,722	3,894	1,016	9,751	9,634	5,165	961	1,457	1,596	5,139	8,922	5,567	.
2021	1	6,004	8,967	4,361	6,369	6,363	1,346	4,289	5,187	3,290	607	5,964	2,965	8,509	5,811	4,852	.
2021	2	3,627	5,806	6,174	6,994	9,474	6,400	8,195	8,863	7,121	6,102	1,458	2,679	5,559	3,009	6,096	.
2021	3	7,011	8,767	9,022	6,870	6,224	5,644	8,820	3,765	9,981	9,738	9,267	2,631	6,917	9,073	2,876	.
2021	4	8,772	8,542	9,006	1,220	8,388	8,341	6,966	2,644	4,422	7,056	3,575	557	7,208	4,555	4,267	.
2021	5	9,004	1,088	9,170	6,064	8,056	2,772	8,439	3,932	7,058	7,320	3,998	2,991	6,405	4,013	3,685	.
2021	6	8,055	5,423	8,775	5,081	1,200	4,341	8,143	5,371	4,464	8,943	7,687	3,747	3,049	9,490	6,421	.
2021	7	2,391	6,513	9,475	4,336	3,688	1,372	7,573	1,217	7,809	8,323	6,039	2,195	7,226	5,075	1,517	.
2021	8	1,258	1,741	3,747	2,124	4,465	1,676	7,280	2,032	7,234	6,664	1,676	9,135	3,393	2,354	6,221	.
2021	9	2,130	5,995	5,034	7,233	4,676	2,955	2,575	2,341	9,201	8,077	8,962	6,975	9,135	6,152	2,712	.
2021	10	1,734	6,838	8,114	990	8,824	3,888	4,523	1,309	9,441	8,017	8,457	4,397	5,002	5,958	8,036	.
2021	11	8,374	1,817	5,479	5,692	8,212	6,676	6,199	6,158	1,628	7,209	6,744	8,350	6,823	5,444	4,933	.
2021	12	3,434	2,911	3,664	9,932	8,776	6,783	5,169	7,068	2,278	7,960	1,696	6,843	4,252	1,441	5,565	.
2022	1																0.015
2022	2																0.015
2022	3																0.015
2022	4																0.015
2022	5																0.015
2022	6																0.015
2022	7																0.015
2022	8																0.015
2022	9																0.015
2022	10																0.015
2022	11																0.015
2022	12																0.015
2023	1																0.025
2023	2																0.025
2023	3																0.025
2023	4																0.025
2023	5																0.025
2023	6																0.025
2023	7																0.025
2023	8																0.025
2023	9																0.025
2023	10																0.025
2023	11																0.025
2023	12																0.025

[PaigeMiller]

Please provide data as working SAS data step code, as requested in your previous question (Examples and instructions)

[sas_user_1001]

I can't get the macro in the instructions you posted to work on my end. The data I posted is from an .xlsx workbook, it should copy and paste into Excel so you can load the data into SAS from there. Sorry I can't be of more help in getting you the format needed.

[PaigeMiller]

---

@sas_user_1001 wrote:

I can't get the macro in the instructions you posted to work on my end. The data I posted is from an .xlsx workbook, it should copy and paste into Excel so you can load the data into SAS from there. Sorry I can't be of more help in getting you the format needed.

---

We can't work from Excel files. We need SAS data sets.

[sas_user_1001]

data have;
   infile cards dsd dlm='|';
   input (year month A_Z1-A_Z4 A_total B_Z1-B_Z4 B_total C_Z1-C_Z4 C_total growth)(:comma16.);
   cards;
2020|1|6,020|9,106|5,898|3,143|9,753|9,878|5,829|3,426|7,322|4,974|2,785|1,287|5,564|7,965|4,527|.
2020|2|3,007|2,838|4,892|794|5,124|7,266|6,011|1,736|1,047|9,190|8,763|4,317|6,774|8,262|4,680|.
2020|3|6,658|3,809|2,590|2,783|806|4,792|1,005|4,374|2,372|5,845|2,408|6,940|4,164|9,576|2,398|.
2020|4|8,492|3,414|6,853|1,958|7,484|4,441|7,090|3,733|8,133|4,804|1,637|1,629|2,594|3,117|6,513|.
2020|5|8,503|5,162|4,947|6,870|5,892|3,820|3,062|6,356|1,531|3,749|1,233|6,340|2,595|4,242|971|.
2020|6|6,342|3,966|9,375|2,473|3,348|5,440|1,196|7,345|3,584|5,235|5,040|6,508|8,238|985|5,715|.
2020|7|515|2,878|3,718|1,572|4,348|5,896|8,399|9,296|2,552|2,415|3,252|759|3,608|822|7,619|.
2020|8|8,541|3,038|6,756|5,738|3,288|9,380|995|8,756|9,426|6,289|9,695|9,450|9,611|3,742|6,618|.
2020|9|8,010|7,618|8,719|3,751|1,516|7,122|3,727|3,519|9,199|4,323|6,757|8,114|6,945|7,839|1,703|.
2020|10|3,013|1,697|8,992|4,358|4,013|1,977|7,395|8,515|751|5,401|5,522|5,786|2,659|7,578|3,327|.
2020|11|5,344|6,324|8,052|2,488|5,327|4,439|8,716|4,614|1,323|2,754|4,496|6,311|1,823|3,611|7,636|.
2020|12|8,277|7,321|2,051|7,722|3,894|1,016|9,751|9,634|5,165|961|1,457|1,596|5,139|8,922|5,567|.
2021|1|6,004|8,967|4,361|6,369|6,363|1,346|4,289|5,187|3,290|607|5,964|2,965|8,509|5,811|4,852|.
2021|2|3,627|5,806|6,174|6,994|9,474|6,400|8,195|8,863|7,121|6,102|1,458|2,679|5,559|3,009|6,096|.
2021|3|7,011|8,767|9,022|6,870|6,224|5,644|8,820|3,765|9,981|9,738|9,267|2,631|6,917|9,073|2,876|.
2021|4|8,772|8,542|9,006|1,220|8,388|8,341|6,966|2,644|4,422|7,056|3,575|557|7,208|4,555|4,267|.
2021|5|9,004|1,088|9,170|6,064|8,056|2,772|8,439|3,932|7,058|7,320|3,998|2,991|6,405|4,013|3,685|.
2021|6|8,055|5,423|8,775|5,081|1,200|4,341|8,143|5,371|4,464|8,943|7,687|3,747|3,049|9,490|6,421|.
2021|7|2,391|6,513|9,475|4,336|3,688|1,372|7,573|1,217|7,809|8,323|6,039|2,195|7,226|5,075|1,517|.
2021|8|1,258|1,741|3,747|2,124|4,465|1,676|7,280|2,032|7,234|6,664|1,676|9,135|3,393|2,354|6,221|.
2021|9|2,130|5,995|5,034|7,233|4,676|2,955|2,575|2,341|9,201|8,077|8,962|6,975|9,135|6,152|2,712|.
2021|10|1,734|6,838|8,114|990|8,824|3,888|4,523|1,309|9,441|8,017|8,457|4,397|5,002|5,958|8,036|.
2021|11|8,374|1,817|5,479|5,692|8,212|6,676|6,199|6,158|1,628|7,209|6,744|8,350|6,823|5,444|4,933|.
2021|12|3,434|2,911|3,664|9,932|8,776|6,783|5,169|7,068|2,278|7,960|1,696|6,843|4,252|1,441|5,565|.
2022|1| | | | | | | | | | | | | | | |0.015
2022|2| | | | | | | | | | | | | | | |0.015
2022|3| | | | | | | | | | | | | | | |0.015
2022|4| | | | | | | | | | | | | | | |0.015
2022|5| | | | | | | | | | | | | | | |0.015
2022|6| | | | | | | | | | | | | | | |0.015
2022|7| | | | | | | | | | | | | | | |0.015
2022|8| | | | | | | | | | | | | | | |0.015
2022|9| | | | | | | | | | | | | | | |0.015
2022|10| | | | | | | | | | | | | | | |0.015
2022|11| | | | | | | | | | | | | | | |0.015
2022|12| | | | | | | | | | | | | | | |0.015
2023|1| | | | | | | | | | | | | | | |0.025
2023|2| | | | | | | | | | | | | | | |0.025
2023|3| | | | | | | | | | | | | | | |0.025
2023|4| | | | | | | | | | | | | | | |0.025
2023|5| | | | | | | | | | | | | | | |0.025
2023|6| | | | | | | | | | | | | | | |0.025
2023|7| | | | | | | | | | | | | | | |0.025
2023|8| | | | | | | | | | | | | | | |0.025
2023|9| | | | | | | | | | | | | | | |0.025
2023|10| | | | | | | | | | | | | | | |0.025
2023|11| | | | | | | | | | | | | | | |0.025
2023|12| | | | | | | | | | | | | | | |0.025
;;;;
   run;

[data_null__]

It is easy to convert the tab delimited table in your post to a program with UltraEdit.  I don't understand your description of the problem.  Seems like multiply 2022 by 1.015 and you are done.  Show worked example of a couple of obs.

data have;
   infile cards dsd dlm='|';
   input (year month A_Z1-A_Z4 A_total B_Z1-B_Z4 B_total C_Z1-C_Z4 C_total growth)(:comma16.);
   cards;
2020|1|6,020|9,106|5,898|3,143|9,753|9,878|5,829|3,426|7,322|4,974|2,785|1,287|5,564|7,965|4,527|.
2020|2|3,007|2,838|4,892|794|5,124|7,266|6,011|1,736|1,047|9,190|8,763|4,317|6,774|8,262|4,680|.
2020|3|6,658|3,809|2,590|2,783|806|4,792|1,005|4,374|2,372|5,845|2,408|6,940|4,164|9,576|2,398|.
2020|4|8,492|3,414|6,853|1,958|7,484|4,441|7,090|3,733|8,133|4,804|1,637|1,629|2,594|3,117|6,513|.
2020|5|8,503|5,162|4,947|6,870|5,892|3,820|3,062|6,356|1,531|3,749|1,233|6,340|2,595|4,242|971|.
2020|6|6,342|3,966|9,375|2,473|3,348|5,440|1,196|7,345|3,584|5,235|5,040|6,508|8,238|985|5,715|.
2020|7|515|2,878|3,718|1,572|4,348|5,896|8,399|9,296|2,552|2,415|3,252|759|3,608|822|7,619|.
2020|8|8,541|3,038|6,756|5,738|3,288|9,380|995|8,756|9,426|6,289|9,695|9,450|9,611|3,742|6,618|.
2020|9|8,010|7,618|8,719|3,751|1,516|7,122|3,727|3,519|9,199|4,323|6,757|8,114|6,945|7,839|1,703|.
2020|10|3,013|1,697|8,992|4,358|4,013|1,977|7,395|8,515|751|5,401|5,522|5,786|2,659|7,578|3,327|.
2020|11|5,344|6,324|8,052|2,488|5,327|4,439|8,716|4,614|1,323|2,754|4,496|6,311|1,823|3,611|7,636|.
2020|12|8,277|7,321|2,051|7,722|3,894|1,016|9,751|9,634|5,165|961|1,457|1,596|5,139|8,922|5,567|.
2021|1|6,004|8,967|4,361|6,369|6,363|1,346|4,289|5,187|3,290|607|5,964|2,965|8,509|5,811|4,852|.
2021|2|3,627|5,806|6,174|6,994|9,474|6,400|8,195|8,863|7,121|6,102|1,458|2,679|5,559|3,009|6,096|.
2021|3|7,011|8,767|9,022|6,870|6,224|5,644|8,820|3,765|9,981|9,738|9,267|2,631|6,917|9,073|2,876|.
2021|4|8,772|8,542|9,006|1,220|8,388|8,341|6,966|2,644|4,422|7,056|3,575|557|7,208|4,555|4,267|.
2021|5|9,004|1,088|9,170|6,064|8,056|2,772|8,439|3,932|7,058|7,320|3,998|2,991|6,405|4,013|3,685|.
2021|6|8,055|5,423|8,775|5,081|1,200|4,341|8,143|5,371|4,464|8,943|7,687|3,747|3,049|9,490|6,421|.
2021|7|2,391|6,513|9,475|4,336|3,688|1,372|7,573|1,217|7,809|8,323|6,039|2,195|7,226|5,075|1,517|.
2021|8|1,258|1,741|3,747|2,124|4,465|1,676|7,280|2,032|7,234|6,664|1,676|9,135|3,393|2,354|6,221|.
2021|9|2,130|5,995|5,034|7,233|4,676|2,955|2,575|2,341|9,201|8,077|8,962|6,975|9,135|6,152|2,712|.
2021|10|1,734|6,838|8,114|990|8,824|3,888|4,523|1,309|9,441|8,017|8,457|4,397|5,002|5,958|8,036|.
2021|11|8,374|1,817|5,479|5,692|8,212|6,676|6,199|6,158|1,628|7,209|6,744|8,350|6,823|5,444|4,933|.
2021|12|3,434|2,911|3,664|9,932|8,776|6,783|5,169|7,068|2,278|7,960|1,696|6,843|4,252|1,441|5,565|.
2022|1| | | | | | | | | | | | | | | |0.015
2022|2| | | | | | | | | | | | | | | |0.015
2022|3| | | | | | | | | | | | | | | |0.015
2022|4| | | | | | | | | | | | | | | |0.015
2022|5| | | | | | | | | | | | | | | |0.015
2022|6| | | | | | | | | | | | | | | |0.015
2022|7| | | | | | | | | | | | | | | |0.015
2022|8| | | | | | | | | | | | | | | |0.015
2022|9| | | | | | | | | | | | | | | |0.015
2022|10| | | | | | | | | | | | | | | |0.015
2022|11| | | | | | | | | | | | | | | |0.015
2022|12| | | | | | | | | | | | | | | |0.015
2023|1| | | | | | | | | | | | | | | |0.025
2023|2| | | | | | | | | | | | | | | |0.025
2023|3| | | | | | | | | | | | | | | |0.025
2023|4| | | | | | | | | | | | | | | |0.025
2023|5| | | | | | | | | | | | | | | |0.025
2023|6| | | | | | | | | | | | | | | |0.025
2023|7| | | | | | | | | | | | | | | |0.025
2023|8| | | | | | | | | | | | | | | |0.025
2023|9| | | | | | | | | | | | | | | |0.025
2023|10| | | | | | | | | | | | | | | |0.025
2023|11| | | | | | | | | | | | | | | |0.025
2023|12| | | | | | | | | | | | | | | |0.025
;;;;
   run;

[sas_user_1001]

The bolded values are the forecast for A_Z1 for 2022. I did this in Excel.

For instance, 6094 is computed as 6004*(1+0.015). The next number 3681 is computed as 3627*(1+0.015).

year	month	A_Z1
2020	1	6,020
2020	2	3,007
2020	3	6,658
2020	4	8,492
2020	5	8,503
2020	6	6,342
2020	7	515
2020	8	8,541
2020	9	8,010
2020	10	3,013
2020	11	5,344
2020	12	8,277
2021	1	6,004
2021	2	3,627
2021	3	7,011
2021	4	8,772
2021	5	9,004
2021	6	8,055
2021	7	2,391
2021	8	1,258
2021	9	2,130
2021	10	1,734
2021	11	8,374
2021	12	3,434
2022	1	6094
2022	2	3681
2022	3	7116
2022	4	8904
2022	5	9139
2022	6	8176
2022	7	2427
2022	8	1277
2022	9	2162
2022	10	1760
2022	11	8500
2022	12	3486

Clearly I need to repeat this for additional years and across the other variables (A_Z2, A_Z3, etc.).