Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Calculating Cumulative SUM over a period of Day

Reply
New Contributor
Posts: 3

Calculating Cumulative SUM over a period of Day

[ Edited ]

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

Super User
Posts: 7,854

Re: Calculating Cumulative SUM over a period of Day

Posted in reply to Gaurav_Singh

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Calculating Cumulative SUM over a period of Day

Posted in reply to KurtBremser

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

Super User
Posts: 5,437

Re: Calculating Cumulative SUM over a period of Day

Posted in reply to Gaurav_Singh
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.
Data never sleeps
Contributor
Posts: 53

Re: Calculating Cumulative SUM over a period of Day

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

Ask a Question
Discussion stats
  • 4 replies
  • 601 views
  • 0 likes
  • 4 in conversation