topic Re: Average Variable by Hour Over Multiple Days in SAS Enterprise Guide

Average Variable by Hour Over Multiple Days

acloseCC — Tue, 10 Apr 2018 22:17:34 GMT

I have been given the following data called P_sort: (small portion of it)
assoc_count ... time
5 18JUL2017:23:59:59.0000000
9 19JUL2017:02:59:59.0000000
2 19JUL2017:05:59:59.0000000
1 19JUL2017:08:59:59.0000000
2 19JUL2017:11:59:59.0000000
1 19JUL2017:14:59:59.0000000
4 19JUL2017:17:59:59.0000000
9 19JUL2017:20:59:59.0000000
10 19JUL2017:23:59:59.0000000
15 20JUL2017:02:59:59.0000000

... ...

I am tasked with finding an average of the assoc_count per hour over a specified number of weeks. This average will then be displayed with bar charts.

My current code is:

proc TIMESERIES data=P_sort out=P_sort;
   id time interval=hour
   accumulate=total
   start='018jul2018:00:00:00'dt
   end  ='025jul2018:00:00:00'dt;
   var assoc_count;
run;

/* AVERAGING GOES HERE*/

proc gchart data=P_sort;
	vbar time / sumvar=assoc_count maxis=axis1 raxis=axis2;
run;

I don't know how to implement this. Any suggestions would be helpful.

Re: Average Variable by Hour Over Multiple Days

PaigeMiller — Tue, 10 Apr 2018 22:17:51 GMT

In a data step, compute the hour of the data.

hour = hour(datepart(time));

Then, run PROC SUMMARY on this data set to compute the averages by hour.

Re: Average Variable by Hour Over Multiple Days

acloseCC — Tue, 10 Apr 2018 22:41:24 GMT

What would the "PROC SUMMARY" look like?

Re: Average Variable by Hour Over Multiple Days

PaigeMiller — Wed, 11 Apr 2018 00:39:31 GMT

There are examples in the PROC SUMMARY documentation. Give it a try.

Re: Average Variable by Hour Over Multiple Days

acloseCC — Fri, 13 Apr 2018 20:51:44 GMT

When I use the following code, hour in the table does not change (stays at 185).

DATA P_sort;
	set LIB.W_W_HOURLY;
	hour = hour(datepart(time));
run;

How can this be fixed? I apologize for my limited knowledge of SAS.

Re: Average Variable by Hour Over Multiple Days

SASKiwi — Sun, 15 Apr 2018 01:51:10 GMT

Using DATEPART isn't necessary and it will give you the wrong answer. I think you can use it directly like this:

DATA P_sort;
	set LIB.W_W_HOURLY;
	hour = hour(time);
run;

Re: Average Variable by Hour Over Multiple Days

acloseCC — Tue, 24 Apr 2018 20:30:32 GMT

My final solution is:

DATA P_sort;
	set L***;
	hour = hour(time);
run;

proc sql;
	create table selected_P_sort as
	select assoc_count, hour 
	from P_sort
	where datepart(time) between '01APR2018'd and '15APR2018'd AND hour between 7 and 23;
quit;

proc summary data=selected_P_sort;
	var assoc_count;
	class hour;
	output out=fin_P_sort MEAN=;
run;

Thanks PaigeMiller and SASKiwi.