DATA Step, Macro, Functions and more

Counting Observations by day and then Average by Month

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

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
56481/20/2014
4489611/20/2014
456451/20/2014
4587501/20/2014
4578671/20/2014
564564561/21/2014
45645641/21/2014
258791/21/2014
425456741/21/2014
45458721/21/2014
458751/22/2014
69681/22/2014
325481/22/2014
1215581/22/2014
54561/23/2014
8991/23/2014
455551/23/2014
78941/23/2014
69681/23/2014
445561/23/2014
87899431/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: 9,681

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

View solution in original post


All Replies
Super User
Posts: 17,831

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: 99

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: 9,681

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.

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

Discussion stats
  • 4 replies
  • 1215 views
  • 3 likes
  • 4 in conversation