DATA Step, Macro, Functions and more

Counting Observations by day and then Average by Month

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
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: 10,041

Re: Counting Observations by day and then Average by Month

Posted in reply to RobertNYC
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: 19,851

Re: Counting Observations by day and then Average by Month

Posted in reply to RobertNYC

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

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

Posted in reply to RobertNYC

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,041

Re: Counting Observations by day and then Average by Month

Posted in reply to RobertNYC
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.

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

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