Calculating Cumulative SUM over a period of Day

[Gaurav_Singh]

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

[Kurt_Bremser]

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

[Gaurav_Singh]

Sorry to missed but I am trying this to be done in SAS Visual Analytics.

[LinusH]

Perhaps the functionality you are looking for is covered by the CumulativePeriod operator.
Unfortunately it seems that the most granular outer interval available is _ByMonth_. You could ad a ballot item for _ByDay_...
My guess is that your option for now would be to solve this in the data preparation stage.

[Frank_Boekamp]

Just some thougt: Have you tried using the last (or first) function? Maybe this way you can initialize your sum for each by group?