Solved
Contributor
Posts: 26

# Using previous observations in calculation of current observation

I'm trying to calculate a new value based on an index multiplied by a previous value.  My data currently looks as follows:

So basically, for observation 2, I want to take New_Value = (0.96 * 41000), then for observation 3 I want to take 0.92 times the value I got for New_Value in observation 2, and so on. I'm basically trying to fill in values backward in time using the index, so the missing values in New_Value are no longer missing.

I was trying to use formula such as, New_Value = ( ( Index ) * ( lag1(New_Value) ) ) , but this doesn't seem to work. It doesn't seem to write a new value to New_Value before trying to run the formula for the next observation, so it leaves it as a missing value.

Any help is greatly appreciated.

Accepted Solutions
Solution
‎08-12-2014 09:20 AM
Regular Contributor
Posts: 217

## Re: Using previous observations in calculation of current observation

retain prior_value;

if first record then do;

prior_value = value;

new_value = prior_value;

end;

if any other record then do;

new_value = prior value * index;

prior_value = new_value;                   /* write the new prior_value after creating the new_value */

end;

All Replies
Solution
‎08-12-2014 09:20 AM
Regular Contributor
Posts: 217

## Re: Using previous observations in calculation of current observation

retain prior_value;

if first record then do;

prior_value = value;

new_value = prior_value;

end;

if any other record then do;

new_value = prior value * index;

prior_value = new_value;                   /* write the new prior_value after creating the new_value */

end;

Contributor
Posts: 26

## Re: Using previous observations in calculation of current observation

Just need to add a by statement and this works great. Thanks!

Contributor
Posts: 45

## Re: Using previous observations in calculation of current observation

My version....

data have;

infile datalines missover;

Input customer index New_value;

datalines;

1   1    41000

1   0.96

1   0.92

1   0.84

1   0.91

2   1    32000

2   0.89

2   0.87

2   0.93

2   0.76

3   1    25000

3   0.96

;

data want(drop=prevnval);

set have;

if missing(New_value) then New_value = index * prevnval;

prevnval = New_value;

retain prevnval;

🔒 This topic is solved and locked.

Discussion stats
• 3 replies
• 1356 views
• 3 likes
• 3 in conversation