turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- multiplication based on previous row's value

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 01:05 PM

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;

Accepted Solutions

Solution

09-22-2014
01:52 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 01:52 PM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 01:13 PM

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 --<-----

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 01:44 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 01:40 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 01:52 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 02:05 PM

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!