Forecasting with a growth rate

[sas_user_1001]

I have a table of annual data. For simplicity, column 1 is the year 2000 - 2023, column 2 is widgets produced for each of those years, and in column 3 I have just one value the CAGR for the final year of my data. The last three rows appear as follows:

Year  Widgets   CAGR

2021       93             .

2022      95             .

2023     100         0.03

What I would like to do is forecast widgets produced annually through 2030. Thus, using my final value for 2023 and the growth rate (e.g., 100*(1+0.003) I can get a forecast for 2024. This should give me a forecasted value of 103. Next, for 2025, I would like to take the preceding value in 2024 and multiply that by the CAGR; thus 103*(1+0.03). I want to continue this process all the way through 2030. I am a bit stuck on how to best approach this in SAS and would appreciate any advice. Thanks.

[FreelanceReinh · Accepted solution]

Hello @sas_user_1001,

Try this:

data want;
set have end=last;
output;
if last then do while(year<2030);
  year+1;
  widgets=widgets*(1+CAGR);
  output;
end;
run;

Dataset WANT will contain everything from dataset HAVE and then continue until YEAR=2030 using the value of CAGR from the last observation of HAVE.

[sas_user_1001]

Thanks for your suggestion. For some reason I am getting an insufficient space error when I run the code. It takes a very long time to run before the error pops up, so I'm wondering if something is getting stuck in a perpetual loop? I will try and take another crack at this on Monday. Cheers.

[ballardw]

Show the EXACT code that you ran.

Do while is one of the things if done incorrectly will run forever but we need to see what you actually run.

[FreelanceReinh]

---

@sas_user_1001 wrote:

Thanks for your suggestion. For some reason I am getting an insufficient space error when I run the code. It takes a very long time to run before the error pops up, so I'm wondering if something is getting stuck in a perpetual loop? I will try and take another crack at this on Monday. Cheers.

---

My suggestion was based on your sample data, implemented like this:

data have;
input Year Widgets CAGR;
cards;
2021 93 .
2022 95 .
2023 100 0.03
;

Maybe you have Year values which are in fact datetime values and the pivotal year in the last observation is in the 1950s or earlier? Then, indeed, the DO-WHILE loop would create a lot of observations and the (exponentially growing) value of variable Widgets may become very large.

Here is an example:

data have;
input Year :b8601dt. Widgets CAGR;
format Year dtyear.;
cards;
1948 93 .
1949 95 .
1950 100 0.03
;

So, make sure you know your data (Maxim 3) -- with a focus on the last observation:

data _null_;
set have nobs=n point=n;
put (Year Widgets CAGR)(=best16.);
stop;
run;

Important question: What does the above DATA _NULL_ step write to the log for your real "HAVE" dataset?

For the 1948-1950 mock-up data above it yields:

Year=-315532800 Widgets=100 CAGR=0.03

and the apparent datetime value of variable Year requires a modified approach:

data want;
set have end=last;
output;
if last & year>.z then do while(year(datepart(year))<2030);
  year=intnx('dtyear',year,1,'s');
  widgets=widgets*(1+CAGR);
  output;
end;
run;

Similarly, if variable Year contains SAS date values, you'll need a different modification:

...
if last & year>.z then do while(year(year)<2030);
  year=intnx('year',year,1,'s');
...

The inserted condition year>.z, i.e., "year is not missing," is important because, unlike the original sum statement year+1, the assignment statement year=intnx(...) would create another missing value from a missing year value. Thus, the WHILE condition would always be met, leading to an infinite loop. To make the code even more robust, you could replace (the special missing value) .z in the IF condition by a reasonable lower-bound year or datetime or date value, respectively, as a safeguard against nonsensical Year values such as -1E99.

[sas_user_1001]

Thank you for your detailed response. I read through it this morning and I was able to troubleshoot the error. I think it was probably a combination of two things on my end. (1) my "Year" column was actually char and not numeric, which I believe confuses the counter part of the code; (2) I think I may have had a variable name issue. I have worked through a number of iterations this morning, and it works now, so thank you for your help! I am going to mark your original answer as the solution, because I believe under the right conditions it should have worked the first time. Cheers.

[FreelanceReinh]

Thanks, @sas_user_1001, for explaining the issue. After your first reply I had also thought about a potential type mismatch, but then was relieved to see that if Year was  a character variable, the sum statement year+1 would terminate the DATA step with an error message

378     year+1;
        -
        400
ERROR 400-185: The SUM statement requires numeric expression.

rather than lead to an infinite loop. Sorry for "causing" that some other way.