Help using Base SAS procedures

multiplication based on previous row's value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

multiplication based on previous row's value

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.

MonthGrowth_AGrowth_BIndex_AIndex_B
Jan..1.00.
Feb20%.1.201.00
Mar30%10%1.561.10
Apr-50%30%0.781.43
May10%-5%0.861.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;


Accepted Solutions
Solution
‎09-22-2014 01:52 PM
Trusted Advisor
Posts: 1,204

Re: multiplication based on previous row's value

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;

View solution in original post


All Replies
Valued Guide
Posts: 3,208

Re: multiplication based on previous row's value

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.

---->-- ja karman --<-----
Occasional Contributor
Posts: 14

Re: multiplication based on previous row's value

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

Super User
Posts: 17,842

Re: multiplication based on previous row's value

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.

Solution
‎09-22-2014 01:52 PM
Trusted Advisor
Posts: 1,204

Re: multiplication based on previous row's value

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;

Occasional Contributor
Posts: 14

Re: multiplication based on previous row's value

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2439 views
  • 7 likes
  • 4 in conversation