topic Re: Problems when working with values from previously calculated rows in SAS Programming

Problems when working with values from previously calculated rows

MaxiHösi — Tue, 29 Jul 2025 10:51:08 GMT

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?

Re: Problems when working with values from previously calculated rows

FreelanceReinh — Tue, 29 Jul 2025 11:24:12 GMT

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;

Re: Problems when working with values from previously calculated rows

MaxiHösi — Tue, 29 Jul 2025 13:10:19 GMT

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