DATA Step, Macro, Functions and more

How to calculate lags on computed column in a same step

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

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.

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

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

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.

View solution in original post


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

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: 24

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

thanks much!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 247 views
  • 1 like
  • 2 in conversation