cumulative sum

[davidnamh]

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;

[RW9]

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;

[davidnamh]

Thank you so much for your help !

[Ksharp]

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

[Haikuo · Accepted solution]

One line shorter ,

data want;

set want;

by acct notsorted;

     retain want;

      want=ifn(first.acct,amt2,want+lag(amt1));

run;

Haikuo