topic sum and running balance by trandate in SAS Procedures

sum and running balance by trandate

franph — Tue, 09 Jun 2015 22:51:25 GMT

Hi, not sure if my previous post uploaded, sorry if you see it 2x ..... I have reworded a bit too

1. I want to sum amounts by trandate and show in new column called dailybal

2. then in a new column called accruingbal show a running balance using values in dailybal ... only if there is a value in dailybal

data example

trandate amount

01/01/2015 5

04/01/2015 2

04/01/2015 7

06/01/2015 9

07/01/2015 3

07/01/2015 4

09/01/2015 6

desired result

trandate amount dailybal accruingbal

01/01/2015 5 5 5

04/01/2015 2

04/01/2015 7 9 14

06/01/2015 9 9 23

07/01/2015 3

07/01/2015 4 7 30

09/01/2015 6 6 6

thanks for your help 🙂

Re: sum and running balance by trandate

AskoLötjönen — Wed, 10 Jun 2015 13:18:33 GMT

data example;

attrib trandate informat=ddmmyy10. format=ddmmyy10.;

infile cards dlm=' ';

input trandate amount 16;

cards;

01/01/2015 5

04/01/2015 2

04/01/2015 7

06/01/2015 9

07/01/2015 3

07/01/2015 4

09/01/2015 6

;

run;

data want;

retain trandate amount dailybal accruingbal;

set example;

by trandate;

if first.trandate then dailybal=0;

dailybal+amount;

accruingbal+amount;

run;

data want;

set want;

by trandate;

if first.trandate and not last.trandate then do;

dailybal=.;

accruingbal=.;

end;

run;

Re: sum and running balance by trandate

Ksharp — Wed, 10 Jun 2015 13:43:41 GMT

Code: Program

data have;
input trandate   : mmddyy10.   amount;
format trandate   mmddyy10.  ;
cards;
01/01/2015 5
04/01/2015 2
04/01/2015 7
06/01/2015 9
07/01/2015 3
07/01/2015 4
09/01/2015 6
;
run;
proc summary data=have;
by trandate;
var amount;
output out=temp(drop=_:) sum=;
run;
data want;
 set temp;
 cumsum+amount;
run;

Re: sum and running balance by trandate

franph — Thu, 11 Jun 2015 22:41:31 GMT

thanks for both your suggestions, helped alot