Hello,
I am facing issue calculating sum over a period of day in case i have multiple rows for a day with different time.
For Ex. i am having below rows in my data.
Date Amount <Need the below output>
2 Nov 2015 02:30:15 100 100
2 Nov 2015 03:40:15 150 250
2 Nov 2015 08:25:10 250 500
9 Dec 2015 01:14:10 200 200 -- Value should reset when day changes
9 Dec 2015 10:15:05 100 300
Simple done with BY processing:
data have;
input day :date9. time :time8. amount;
cards;
02Nov2015 02:30:15 100
02Nov2015 03:40:15 150
02Nov2015 08:25:10 250
09Dec2015 01:14:10 200
09Dec2015 10:15:05 100
;
run;
data want;
set have;
by day;
retain sum;
if first.day then sum = 0;
sum + amount;
run;
proc print noobs;
run;
Result:
day time amount sum 20394 9015 100 100 20394 13215 150 250 20394 30310 250 500 20431 4450 200 200 20431 36905 100 300
Sorry to missed but I am trying this to be done in SAS Visual Analytics.
Just some thougt: Have you tried using the last (or first) function? Maybe this way you can initialize your sum for each by group?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.