BookmarkSubscribeRSS Feed
Gaurav_Singh
Fluorite | Level 6

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

4 REPLIES 4
Kurt_Bremser
Super User

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
Fluorite | Level 6

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

LinusH
Tourmaline | Level 20
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
Frank_Boekamp
Quartz | Level 8

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 4 replies
  • 2522 views
  • 0 likes
  • 4 in conversation