Hi, I am trying to calculate like "want_variable".
1) take amt2 value in want_variable when new acct start
2) sum of previous amt1 + amt2 = current value for want_variable
Thank you so much !
data want;
infile datalines delimiter=',';
input acct amt1 amt2 want_variable;
datalines;
123,100,2000,2000
123,-50,2000,2100
123,20,2000,2050
123,30,2000,2070
123,40,2000,2100
123,-10,2000,2140
234,30,1500,1500
234,-30,1500,1530
234,200,1500,1500
234,-80,1500,1700
234,20,1500,1620
234,10,1500,1640
;
run;
One line shorter ,
data want;
set want;
by acct notsorted;
retain want;
want=ifn(first.acct,amt2,want+lag(amt1));
run;
Haikuo
Hi,
Sorry, your test data doesn't match the logic given. Here is a short code to what you require, however there are different results, for instance 2000 + -50 = 1950 not 2050 given in your test data.
data want;
infile datalines delimiter=',';
input acct amt1 amt2 want_variable;
datalines;
123,100,2000,2000
123,-50,2000,2100
123,20,2000,2050
123,30,2000,2070
123,40,2000,2100
123,-10,2000,2140
234,30,1500,1500
234,-30,1500,1530
234,200,1500,1500
234,-80,1500,1700
234,20,1500,1620
234,10,1500,1640
;
run;
data inter;
set want;
id=_n_;
run;
proc sql;
create table INTER2 as
select BASE.*,
PREV.AMT1 as PREV_AMT1,
PREV.AMT2 as PREV_AMT2,
case when PREV.AMT1 is null and PREV.AMT2 is null then BASE.AMT2
else PREV.AMT1 + PREV.AMT2 end as WANT_CALCULATED
from WORK.INTER BASE
left join WORK.INTER PREV
on BASE.ACCT=PREV.ACCT
and BASE.ID=PREV.ID+1;
quit;
Thank you so much for your help !
data want; infile datalines delimiter=','; input acct amt1 amt2 ; datalines; 123,100,2000 123,-50,2000 123,20,2000 123,30,2000 123,40,2000 123,-10,2000 234,30,1500 234,-30,1500 234,200,1500 234,-80,1500 234,20,1500 234,10,1500 ; run; data want; set want; by acct; retain want . ; want=sum(lag(amt1),want); if first.acct then want=amt2; run;
Xia Keshan
One line shorter ,
data want;
set want;
by acct notsorted;
retain want;
want=ifn(first.acct,amt2,want+lag(amt1));
run;
Haikuo
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.