## Counting Observations by day and then Average by Month

Solved
Frequent Contributor
Posts: 103

# Counting Observations by day and then Average by Month

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.

Accepted Solutions
Solution
‎04-08-2014 07:12 AM
Super User
Posts: 10,784

## Re: Counting Observations by day and then Average by Month

```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

All Replies
Super User
Posts: 23,727

## Re: Counting Observations by day and then Average by Month

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?

Frequent Contributor
Posts: 103

## Re: Counting Observations by day and then Average by Month

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!!!

Contributor
Posts: 29

## Re: Counting Observations by day and then Average by Month

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.

Solution
‎04-08-2014 07:12 AM
Super User
Posts: 10,784

## Re: Counting Observations by day and then Average by Month

```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

🔒 This topic is solved and locked.