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.
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
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?
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!!!
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.