Lag to calculate, then lag calculated value

satkin2 — Fri, 19 Oct 2018 09:46:52 GMT

Hello,

I'm sure this is fairly straight forward but I just can't get my head around how.

I've got a table with a base value for the first record in a category. All other rows within this category have an adjustment rate. I want to work out the new value based upon this adjustment rate. But it's sequential, so row 1 value * row 2 rate = row 2 value, row 2 value * row 3 rate = row 3 value etc. It's not the easiest thing to articulate, so here's an example.

Starting table

Class	Month	Adjustment_Rate	Base_Value
Category 1	Jan-18		10.00
Category 1	Feb-18	0.96
Category 1	Mar-18	0.99
Category 1	Apr-18	1.02
Category 1	May-18	1.05
Category 2	Jan-18		15.00
Category 2	Feb-18	0.94
Category 2	Mar-18	1.00
Category 2	Apr-18	1.03
Category 2	May-18	1.03

This is what I want in the end (I'd subsequently drop Base_Value)

Class	Month	Adjustment_Rate	Base_Value	New_Value
Category 1	Jan-18		10.00	10.00
Category 1	Feb-18	0.96		9.60
Category 1	Mar-18	0.99		9.50
Category 1	Apr-18	1.02		9.69
Category 1	May-18	1.05		10.18
Category 2	Jan-18		15.00	15.00
Category 2	Feb-18	0.94		14.10
Category 2	Mar-18	1.00		14.10
Category 2	Apr-18	1.03		14.52
Category 2	May-18	1.03		14.96

I'm assuming I need to use a lag function, e.g. lag Base_Value, but on observation 2 I need to lag the new value.

Thanks in advance!

Edit: Fixing typos.

Re: Lag to calculate, then lag calculated value

Kurt_Bremser — Fri, 19 Oct 2018 10:02:05 GMT

No need for lag(), but for retain:

data want;
set have;
by class;
retain new_value;
if first.class
then new_value = base_value;
else new_value = new_value * adjustment_rate;
run;

Re: Lag to calculate, then lag calculated value

satkin2 — Fri, 19 Oct 2018 10:12:08 GMT

Perfect. Thank you very much.

topic Re: Lag to calculate, then lag calculated value in SAS Programming

Lag to calculate, then lag calculated value

Re: Lag to calculate, then lag calculated value

Re: Lag to calculate, then lag calculated value