topic cumulative sum in SAS Programming

cumulative sum

davidnamh — Wed, 09 Apr 2014 08:05:18 GMT

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;

Re: cumulative sum

RW9 — Wed, 09 Apr 2014 08:55:32 GMT

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;

Re: cumulative sum

Ksharp — Wed, 09 Apr 2014 11:29:28 GMT

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

Re: cumulative sum

Haikuo — Wed, 09 Apr 2014 15:26:55 GMT

One line shorter ,

data want;

set want;

by acct notsorted;

retain want;

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

run;

Haikuo

Re: cumulative sum

davidnamh — Wed, 09 Apr 2014 15:46:23 GMT

Thank you so much for your help !