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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.