Using previous observations in calculation of current observation

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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:

Capture5.PNG

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;

View solution in original post


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.

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

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