Help using Base SAS procedures

sum and running balance by trandate

Reply
Occasional Contributor
Posts: 6

sum and running balance by trandate

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 :-)

Contributor
Posts: 44

Re: sum and running balance by trandate

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;

Super User
Posts: 9,681

Re: sum and running balance by trandate

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;
Occasional Contributor
Posts: 6

Re: sum and running balance by trandate

thanks for both your suggestions, helped alot

Ask a Question
Discussion stats
  • 3 replies
  • 192 views
  • 0 likes
  • 3 in conversation