topic Re: Multiplying a row by another rows previous value in New SAS User

Multiplying a row by another rows previous value

ted_lariat — Mon, 13 Sep 2021 14:15:31 GMT

Hi everyone,

I'm trying to multiply my ratio column with lagged have value to get to the want colum. What approach should I take?

data have;
input year ratio have want;
cards;
2021 1 5  5
2022 2 . 10
2023 3 . 30
2024 4 . 120
2025 5 . 600
;
run;

Thank you for your time.

Re: Multiplying a row by another rows previous value

PaigeMiller — Mon, 13 Sep 2021 14:31:50 GMT

... multiply my ratio column with lagged have value ...

So doing the math, on row 4 the lagged have value would have to be 30. I don't really see how the lagged have value gets to be 30. Please explain further.

Re: Multiplying a row by another rows previous value

ted_lariat — Mon, 13 Sep 2021 14:37:42 GMT

Apologies, I'll clarify.

data have;
input year ratio have have2 want;
cards;
2021 1 5 . 5
2022 2 . 5 10
2023 3 . 10 30
2024 4 . 30 120
2025 5 . 120 600
;
run;

I've updated the sample code. I only have ratio and have, I'm trying to generate have2, and finally want. It's been a while since I've written a sas code, so I'm a bit rusty.

To elaborate further,

for 2022, I want to multiply, ratio2022 with have2021

for 2023, I want to multiply, ratio 2023 with have 2022 so on.

Re: Multiplying a row by another rows previous value

Tom — Mon, 13 Sep 2021 15:40:43 GMT

Why is the value of HAVE missing for all but the first observation?

data have;
  input year ratio have expect;
cards;
2021 1 5  5
2022 2 . 10
2023 3 . 30
2024 4 . 120
2025 5 . 600
;

data want;
  set have;
  retain prod;
  if _n_=1 then prod=have;
  else prod=prod*ratio;
run;

What if RATIO on the first observation is not 1 what do you want for the new running product variable? If you want it to be HAVE*RATIO then remove the ELSE keyword.

Re: Multiplying a row by another rows previous value

ted_lariat — Tue, 14 Sep 2021 05:37:06 GMT

thanks, it is working like a charm. much appreciated.