Solved
Contributor
Posts: 27

# How to calculate lags on computed column in a same step

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.

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

Accepted Solutions
Solution
‎10-25-2016 02:30 PM
Super User
Posts: 6,921

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

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.

All Replies
Solution
‎10-25-2016 02:30 PM
Super User
Posts: 6,921

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

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.

Contributor
Posts: 27