Problems when working with values from previously calculated rows

[MaxiHösi]

Hello everyone,

I have already tried everything possible but i can't make any progress here. I want to replicate a formula in SAS that originates from Excel. The problem is that the formula relies on values that were calculated in the previous row. To make matters more complicated, there are different type of cars in the table. The table is structured as follows: There is one column with the car_types, one column  with the quarters, one column  with the values, one with the factor and in the 5th column I want to output the calculated value as result.

The formula is as follows:

result = Value1 + 0.6 * Value2 (where value2 is composed of the sum from the row above of Value1 + 0.6 * value2).

This is what i have:

car_type	Quarters	Value 1	factor
Skoda	Q3 2022	-0.02	0.6
Skoda	Q4 2022	-0.24	0.6
Skoda	Q1 2023	0.14	0.6
Skoda	Q2 2023	0.45	0.6
Vauxhall	...	...	...

This is what i want:

car_type	Quarters	Value 1	Value 2	factor	result
Skoda	Q3 2022	-0.02	0	0.6	-0.02
Skoda	Q4 2022	-0.24	-0.02	0.6	-0.252
Skoda	Q1 2023	0.14	-0.252	0.6	-0.0112
Skoda	Q2 2023	0.45	-0.0112	0.6	0.44328
Vauxhall	...	...	...	...	...
...	...	...	...	...	...

When a new car_type starts, the initial value of value2 is 0.

Do you have any idea how i can implement this in sas?

[FreelanceReinh · Accepted solution]

Hello @MaxiHösi,

Use the retained value of result for Value2:

data have;
input car_type :$20. Quarters :yyq. Value1 factor;
format Quarters nldateyqm.;
cards;
Skoda    2022Q3 -0.02 0.6
Skoda    2022Q4 -0.24 0.6
Skoda    2023Q1  0.14 0.6
Skoda    2023Q2  0.45 0.6
Vauxhall 2022Q1  0.31 0.4
Vauxhall 2022Q2 -0.27 0.4
;

data want;
set have;
by car_type Quarters;
Value2=ifn(first.car_type,0,result);
result=Value1+factor*Value2;
retain result;
run;

[MaxiHösi]

Thank you for your  quick response. Since I had missing values, I had to use sum(...), but otherwise, everything worked wonderfully.