## cumulative sum

Solved
Occasional Contributor
Posts: 17

# cumulative sum

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;

Accepted Solutions
Solution
‎04-09-2014 11:26 AM
Posts: 3,167

## Re: cumulative sum

One line shorter ,

data want;

set want;

by acct notsorted;

retain want;

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

run;

Haikuo

All Replies
Super User
Posts: 9,599

## Re: cumulative sum

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;

Occasional Contributor
Posts: 17

## Re: cumulative sum

Thank you so much for your help !

Super User
Posts: 10,778

## Re: cumulative sum

```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

Solution
‎04-09-2014 11:26 AM
Posts: 3,167

## Re: cumulative sum

One line shorter ,

data want;

set want;

by acct notsorted;

retain want;

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

run;

Haikuo

🔒 This topic is solved and locked.