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
... View more