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 🙂
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;
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;
thanks for both your suggestions, helped alot
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.