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 |
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.
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.
thanks much!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.