DATA Step, Macro, Functions and more

cumulative sum

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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
Respected Advisor
Posts: 3,156

Re: cumulative sum

One line shorter Smiley Happy,

data want;

set want;

by acct notsorted;

     retain want;

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

run;

Haikuo

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: cumulative sum

Posted in reply to davidnamh

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,023

Re: cumulative sum

Posted in reply to davidnamh
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
Respected Advisor
Posts: 3,156

Re: cumulative sum

One line shorter Smiley Happy,

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 299 views
  • 6 likes
  • 4 in conversation