topic How to calculate lags on computed column in a same step in SAS Programming

How to calculate lags on computed column in a same step

anu1999 — Tue, 25 Oct 2016 16:58:11 GMT

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

Re: How to calculate lags on computed column in a same step

Astounding — Tue, 25 Oct 2016 18:02:51 GMT

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.

Re: How to calculate lags on computed column in a same step

anu1999 — Tue, 25 Oct 2016 18:31:15 GMT

thanks much!!