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!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.