topic Re: Calculating Cumulative SUM over a period of Day in SAS Visual Analytics

Calculating Cumulative SUM over a period of Day

Gaurav_Singh — Tue, 27 Sep 2016 07:08:28 GMT

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

Re: Calculating Cumulative SUM over a period of Day

Kurt_Bremser — Tue, 27 Sep 2016 08:03:18 GMT

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

Re: Calculating Cumulative SUM over a period of Day

Gaurav_Singh — Wed, 28 Sep 2016 10:55:42 GMT

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

Re: Calculating Cumulative SUM over a period of Day

LinusH — Sun, 02 Oct 2016 19:39:01 GMT

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.

Re: Calculating Cumulative SUM over a period of Day

Frank_Boekamp — Mon, 03 Oct 2016 10:58:05 GMT

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