Hourly results from 15 minute interval data

[DME790]

I have data source that has 15 minute intervals which I would like to group by hour.

If I use the format EVENT_TM hour.; format it sums the 8:30 and 8:45 to the 9am calculation.

Data Work.Interval_Final;

Set Work.Final_Int;

format EVENT_TM hour.;

Run;

How do I get the time inetrvals to add :00 :15 :30 and :45 to the correct hour.

Sample data below (Apologies I can't convert the data to the right format for others to use)

EVENT_DT	EVENT_TM	CALLS_ANSWERED_CNT	CALLS_ABANDONED_CNT	TOTAL_ANSWER_TM
11/01/2017	8:00:00	937	144	734966
11/01/2017	8:15:00	425	158	639406
11/01/2017	8:30:00	402	110	627846
11/01/2017	8:45:00	363	121	516766
11/01/2017	9:00:00	407	100	613785
11/01/2017	9:15:00	456	112	629621
11/01/2017	9:30:00	503	129	671057
11/01/2017	9:45:00	472	119	608942
11/01/2017	10:00:00	485	136	702004
11/01/2017	10:15:00	510	132	747481
11/01/2017	10:30:00	361	134	438537
11/01/2017	10:45:00	356	94	451957
11/01/2017	11:00:00	457	121	603397
11/01/2017	11:15:00	592	169	885680
11/01/2017	11:30:00	416	117	549181
11/01/2017	11:45:00	409	132	569009
11/01/2017	12:00:00	447	128	630113
11/01/2017	12:15:00	485	171	733852
11/01/2017	12:30:00	405	118	549902
11/01/2017	12:45:00	320	92	372392

[Reeza · Accepted solution]

Try the TIME2. format instead.

If you want it displayed as 8:00 you'll likely need either a custom format or to do a calculate and create a new variable to work with. 

[ballardw]

If you have a SAS data set these instructions will allow you to create the code for a data step to replicate your data (or a subset for example) https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Is your sample data what you have or what you want? It looks like the time is already as you requested so we would need to see how it starts.

You need to include what you want as an output as it is not clear what you are doing. You say "sums the 8:30 and 8:45 to the 9am calculation" but that would only happen if you sent the data to a proc like means, report or tabulate.

Do you have data at shorter intervals and you need to group it?

Maybe something like this:

data have;
   informat time time.;
   format time time.;
   input time;
   newtime = intnx('minute15',time,0,'B');
   format newtime time.;
datalines;
08:10:15
08:14:00
08:20:21
08:29:59
;
run;