How to calculate lags on computed column in a same step

[anu1999]

Hi,

I am trying to calculate a column from lags which are created in a same step. I am not sure how to do that, might be a do loop would work.

Appreciate your input.

example:

Here I have col1 and col2 populated. I want to calculate "res" column with a logic that if its first ID then res=col1 else res is previous value of res (in this case 100) - previous value of col2 (in this case 20), which is 100-20=80. Next row it should take previous calculated res 80 and subtract previous col2 which is 10 so res=80-10=70.

id	col1	col2	res
1	100	20	100
1	98	10	80
1	50	7	70
1	35	5	63
1	20	4	58
1	5	1	54

[Astounding · Accepted solution]

This ought to work. 

data want;

set have;

by id;

prior_col2 = lag(col2);

retain res;

if first.id then res=100;

else res = res - prior_col2;

drop prior_col2;

run;

It's important that RES is newly created, not part of the incoming data.  Since RES is retained, there's no need to use a LAG function to retrieve its prior value.  The prior value just remains in place on the next observation.

[anu1999]

thanks much!!