Counting Observations by day and then Average by Month

[RobertNYC]

Hi all--

I need to calculate the average touches by month. Below I have a column Touch_ID which has all unique values, so each row represents a "Touch" and date value for each touch.  Example:

Touches_ID	Date
5648	1/20/2014
448961	1/20/2014
45645	1/20/2014
458750	1/20/2014
457867	1/20/2014
56456456	1/21/2014
4564564	1/21/2014
25879	1/21/2014
42545674	1/21/2014
4545872	1/21/2014
45875	1/22/2014
6968	1/22/2014
32548	1/22/2014
121558	1/22/2014
5456	1/23/2014
899	1/23/2014
45555	1/23/2014
7894	1/23/2014
6968	1/23/2014
44556	1/23/2014
8789943	1/23/2014

I need to calculate the average touches  by month. So, I need to get a count of total touched for each day and then calculate the monthly average.

Any assistance is greatly appreciated.

[Reeza]

My brain may be fried...end of day...but why do you need the daily totals? is the "monthly" average a daily average for the month?

[RobertNYC]

Hi. Maybe my Brain is the one thats fried.... yes the average daily average by month -  but I also need a Year To date average as well. Thanks!!!

[Chrishi]

proc sql;

create table new as select *,count(touches_id) as touches, month(date) as month from old group by date;

  quit;

proc sort data=new nodupkey;

by date month touches;

run;

Now you can caluclate the average by using by variable as month.

Hope this solves your problem, If not please elobrate your question or give a sample out put with example for understanding with small values.

[Ksharp · Accepted solution]

data have;
input Touches_ID     Date : mmddyy12.;
format date mmddyy10.;
cards;
5648     1/20/2014
448961     1/20/2014
45645     1/20/2014
458750     1/20/2014
457867     1/20/2014
56456456     1/21/2014
4564564     1/21/2014
25879     1/21/2014
42545674     1/21/2014
4545872     1/21/2014
45875     1/22/2014
6968     1/22/2014
32548     1/22/2014
121558     1/22/2014
5456     1/23/2014
899     1/23/2014
45555     1/23/2014
7894     1/23/2014
6968     1/23/2014
44556     1/23/2014
8789943     1/23/2014
;
run;

proc sql;
create table new as
 select date,count(touches_id) as touches
  from have 
   group by date;

create table average as
 select year(date) as year,month(date) as month,avg(touches) as avg
  from new
   group by year,month;

  quit;

Xia Keshan