Multiplying one step ahead

[sas_user_1001]

I'm trying to figure out the best way to multiply two data points that are adjacent. A simplified version of my data is as follows:

year        x       y

2000      .        5

2001      2         .

2002     3         .

2003     4         .

I want to multiply 5x2 to produce 10 in column y for the year 2001. Similarly, I want to multiply 3 by the new value 10 in column y, to produce 30 in the year 2002. Finally, I want to multiply 4 by the new value of 30 to produce 120 in the year 2003. Any help is much appreciated. Thanks.

[ballardw]

Please note the first data step as a way to provide existing data.

data have;
   input year        x       y;
datalines;
2000      .        5
2001      2         .
2002     3         .
2003     4         .
;

data want;
   set have;
   retain ly;
   if _n_ =1 then ly=y;
   else ly= ly*x;
run;

I'm placing the value of result into a new variable LY so the value persist across data step iterations with the RETAIN statement. If you attempt to Retain a variable in the source data set it gets reset each time a new observation is read from the source.

If you are going to have groups of records that need to "reset" the Ly, such as when apply this algorithm to person, geography, time or similar values you need to include more details as likely BY group processing with the First and Last options will come into play.

[Tom · Accepted solution]

It is probably easiest to make a NEW variable so you can retain its value (and it the retained value will be overwritten when you read in the next observation.)

So something like:

data have;
  input year x y;
cards;
2000 . 5
2001 2 .
2002 3 .
2003 4 .
;

data want;
  set have;
  retain newy ;
  newy=coalesce(y,x*newy);
run;

Result

Obs    year    x    y    newy

 1     2000    .    5       5
 2     2001    2    .      10
 3     2002    3    .      30
 4     2003    4    .     120

[sas_user_1001]

Thanks--I like this solution as I have not used the coalesce function before.