topic Re: Multiplying one step ahead in New SAS User

Multiplying one step ahead

sas_user_1001 — Wed, 20 Mar 2024 22:24:51 GMT

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.

Re: Multiplying one step ahead

ballardw — Wed, 20 Mar 2024 22:46:48 GMT

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.

Re: Multiplying one step ahead

Tom — Wed, 20 Mar 2024 22:53:56 GMT

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

Re: Multiplying one step ahead

sas_user_1001 — Thu, 21 Mar 2024 00:06:55 GMT

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