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 |
Please provide data as working SAS data step code, as requested in your previous question (Examples and instructions)
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.
@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.
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;
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;
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.).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.