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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.