Desktop productivity for business analysts and programmers

Average Variable by Hour Over Multiple Days

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Average Variable by Hour Over Multiple Days

[ Edited ]

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.


Accepted Solutions
Solution
‎04-24-2018 04:29 PM
Occasional Contributor
Posts: 5

Re: Average Variable by Hour Over Multiple Days

[ Edited ]

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.

View solution in original post


All Replies
Respected Advisor
Posts: 2,662

Re: Average Variable by Hour Over Multiple Days

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.

--
Paige Miller
Occasional Contributor
Posts: 5

Re: Average Variable by Hour Over Multiple Days

Posted in reply to PaigeMiller

What would the "PROC SUMMARY" look like?

Respected Advisor
Posts: 2,662

Re: Average Variable by Hour Over Multiple Days

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

--
Paige Miller
Occasional Contributor
Posts: 5

Re: Average Variable by Hour Over Multiple Days

Posted in reply to PaigeMiller

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. 

Super User
Posts: 3,778

Re: Average Variable by Hour Over Multiple Days

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;
Solution
‎04-24-2018 04:29 PM
Occasional Contributor
Posts: 5

Re: Average Variable by Hour Over Multiple Days

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 215 views
  • 0 likes
  • 3 in conversation