multiplication based on previous row's value

[blue_lion]

Hi

I have monthly growth rates (Growth_A and Growth_B) and my aim is to create indicators(Index_A and Index_B) based on these growth rates.

Index always starts with the value of "1" and then take the value of (previous month's index value)*(1+monthly growth rate) after the first month.

For example, Index_A starts with 1 and then 1.2 after 20% growth. Then 1.2*(1+0.3) = 1.56 so on and so forth.

Month	Growth_A	Growth_B	Index_A	Index_B
Jan	.	.	1.00	.
Feb	20%	.	1.20	1.00
Mar	30%	10%	1.56	1.10
Apr	-50%	30%	0.78	1.43
May	10%	-5%	0.86	1.36

Here is the code I wrote, but I get missing values because the lag function somehow does not retain the value of "1" from the very 1st period.

I tried with if..then..else statement and other ways of coding, but I get the same error because the lag function is not working.

It would be great if someone could let me know other ways to code this multiplication based on previously calculated value.

Data new;

set have;

Index_A=ifn(growth_A=".",1,(1+growth_A)*lag(index_A));

run;

[jakarman]

do not use the lag function condtional unless you understand that it is a queue function not the same as retain
The first call will always return a missing. You should solve that in your logic.

[blue_lion]

I see. I did not know the lag function in a conditional statement will always return a missing value.

[Reeza]

1. I would expect growth_A to be a numeric variable so that the first comparison would cause an issue as it's a character comparison.

growth_A="."

Replace with:

growth_a=.

2. I'm not sure how your sample matches what you want as the first value in the table for index_a is not 1. Is that table what you're getting or what you want? Its easiest if you post two tables, the first being what you have and the second being what you want.

[stat_sas · Accepted solution]

data have;
input Month $ Growth_A Growth_B;
datalines;
Jan . .
Feb 0.20 .
Mar 0.30 0.10
Apr -0.50 0.30
May 0.10 -0.05
;

Data new;
set have;
retain index_a index_b 1;
Index_A=(1+coalesce(growth_A,0))*index_A;
Index_B=(1+coalesce(growth_B,0))*index_B;
run;

[blue_lion]

Hi Reeza, thanks for your comment. The table in my original post is the one I want to create.

What I have is the first 3 columns (month, growth_A, growth_B).

I have historical price of A from Jan, but when I calculated monthly growth rate as in the table, it starts from the following month Feb.

So I want to have Jan as a base month so the value of "1" is assigned.

For B, I only have historical price from Feb, so the growth rate starts from Mar.

For index B, I understand that my code does not give "." for the month of Jan, but I was planning to deal with it after solving the lag function issue.

Hi Stat@sas,

Thank you so much for your help! I just couldn't think of ways to code this without lag function, but it really makes sense now.

I did not know "coalesce" function, but it is useful. Thanks again!