Hello,   When I run this code,    (/* Next get mean for each month */ proc summary data=find_month nway; var what_month; output out=_mean_ mean=mean_count; run;)   I end up with a dataset consisting of 1 observation.   Shouldn't I end up wtih 12 observations, since I am looking for average for each month?   Also, the following section:   ( /* Combine means and counts */ proc sql; create table _counts1_ as select c.*,m.mean_count from _counts_ as c left join _mean_ as m on month(c.dtspec2)=m.what_month order by c.dtspec2; quit;)   returns an error message saying :     " ERROR: Column dtspec2 could not be found in the table/view identified with the correlation name C. ERROR: Column dtspec2 could not be found in the table/view identified with the correlation name C. ERROR: Column dtspec2 could not be found in the table/view identified with the correlation name C. ERROR: Column what_month could not be found in the table/view identified with the correlation name M. ERROR: Column what_month could not be found in the table/view identified with the correlation name M. ERROR: Column what_month could not be found in the table/view identified with the correlation name M. ERROR: Column dtspec2 could not be found in the table/view identified with the correlation name C. ERROR: Function MONTH requires a numeric expression as argument 1. ... View more

?That sounds like a great strategy. Thanks for that suggestion. Could you possibly point me to a sample code for producing dots (representing average count per month over the course of 5-years) that I could pull into the chart? ... View more

Thanks again. When I try that code, the resulting graph has a flat line running through all the points. It seems that one average value was created, which runs across all months (where as each month should have it's own average count over the span of 5 years). Looking closer at the code, should the first section (obtain counts by month) reference "datamonth" rather than "dtspec2"? Then there would be 12 values in the resulting "counts" dataset. ... View more

?Yes, I would like to calculate the average of monthly case counts over the course of 5-years. The resulting graph would show freqency of cases by month for 2019, with a 5-year average line (2015-2019) overlayed (if possible). DtSpec2 is my anchor date for calculating frequencies. ... View more

Thanks!    Attached and below is a sample of my dataset, de-identified.  The sample contains cases from 2015 (the entire dataset spans 2015-2019).   Is this format ok?  I just attached (and copied) the downloaded excel file of the dataset.     Obs CaseID Status Effective_From_Date Create_Date DISEASE DISEASE_STATUS EVENT_DATE EVENT_TYPE STATE_HIDDEN CASE_HOSPITALIZED_0 SS_BLOODY_DIARRHEA SS_DIARRHEA SS_DIARRHEA_BLOODY SS_DIARRHEA_DURATION SS_DIARRHEA_ONSET_DATE SS_DIARRHEA_ONSET_TIME SS_FEVER SS_THROMBOCYTOPENIA SS_VOMITING SS_VOMITING_ONSET_DATE SS_VOMITING_ONSET_TIME STILL_SYMPTOMATIC SYMPTOMS SYMPTOM_ONSET_DATE SYMPTOM_ONSET_TIME WORK_RELATED_INJURY AGE_YEARS COUNTRY COUNTY GENDER HISPANIC RACE_0 DtSpec Pathogen TravelInt DtSpec2 datayear datamonth datamonth05yr datamonth10yr MMWR2 1 100515896 Closed 7-Jan-15 12-Jan-15 CAMP CONFIRMED 7-Jan-15 SYMPTOM_ONSET_DATE CT YES   YES NO 8 7-Jan-15   YES   NO       YES 7-Jan-15   No 64 USA New Haven County MALE NO WHITE 1/9/2015 Campylobacter No 1/9/2015 2015 1 1.2 1.2 15_01 2 100516106   . .     .             . .                 .     .           1/3/2015 Campylobacter No 1/3/2015 2015 1 1 1.1111   3 100516107 Closed 5-Jan-15 13-Jan-15 CAMP CONFIRMED 5-Jan-15 LAB_TEST_DATE CT NO   YES NO . .   NO   YES       YES .     58 USA Fairfield County FEMALE NO WHITE 1/5/2015 Campylobacter No 1/5/2015 2015 1 1 1.1 15_01 4 100516116 Closed 2-Jan-15 13-Jan-15 CAMP CONFIRMED 2-Jan-15 LAB_TEST_DATE CT NO       . .               UNKNOWN .     62 USA Fairfield County MALE NO WHITE 1/2/2015 Campylobacter Unknown 1/2/2015 2015 1 1 1   5 100516476 Closed 2-Jan-15 15-Jan-15 CAMP CONFIRMED 2-Jan-15 SYMPTOM_ONSET_DATE CT NO   YES NO 5 2-Jan-15 am NO   NO       YES 2-Jan-15 am   56 USA New Haven County FEMALE NO BLACK_AFRICAN_AMERICAN 1/7/2015 Campylobacter No 1/7/2015 2015 1 1 1 15_01 6 100516481 Closed 5-Jan-15 15-Jan-15 CAMP CONFIRMED 5-Jan-15 SYMPTOM_ONSET_DATE CT NO   YES NO 2 5-Jan-15 9:00 PM YES   NO       YES 5-Jan-15     31 USA New Haven County FEMALE NO WHITE 1/7/2015 Campylobacter Yes 1/7/2015 2015 1 1 1 15_01 7 100516484 Closed 8-Jan-15 15-Jan-15 CAMP CONFIRMED 8-Jan-15 LAB_TEST_DATE CT NO       . .               UNKNOWN .     57 USA New London County FEMALE UNKNOWN BLACK_AFRICAN_AMERICAN 1/8/2015 Campylobacter Unknown 1/8/2015 2015 1 1 1 15_01 8 100516485 Closed 4-Jan-15 15-Jan-15 CAMP CONFIRMED 4-Jan-15 SYMPTOM_ONSET_DATE CT YES   YES NO 8 6-Jan-15   NO   NO       YES 4-Jan-15     61 USA New Haven County MALE NO WHITE 1/11/2015 Campylobacter No 1/11/2015 2015 1 1 1 15_02 9 100516544 Closed 7-Jan-15 16-Jan-15 CAMP CONFIRMED 7-Jan-15 LAB_TEST_DATE CT NO       . .               UNKNOWN .     94 USA Hartford County FEMALE UNKNOWN Unknown 1/7/2015 Campylobacter Unknown 1/7/2015 2015 1 1 1 15_01 Obs CaseID Status Effective_From_Date Create_Date DISEASE DISEASE_STATUS EVENT_DATE EVENT_TYPE STATE_HIDDEN CASE_HOSPITALIZED_0 SS_BLOODY_DIARRHEA SS_DIARRHEA SS_DIARRHEA_BLOODY SS_DIARRHEA_DURATION SS_DIARRHEA_ONSET_DATE SS_DIARRHEA_ONSET_TIME SS_FEVER SS_THROMBOCYTOPENIA SS_VOMITING SS_VOMITING_ONSET_DATE SS_VOMITING_ONSET_TIME STILL_SYMPTOMATIC SYMPTOMS SYMPTOM_ONSET_DATE SYMPTOM_ONSET_TIME WORK_RELATED_INJURY AGE_YEARS COUNTRY COUNTY GENDER HISPANIC RACE_0 DtSpec Pathogen TravelInt DtSpec2 datayear datamonth datamonth05yr datamonth10yr MMWR2 1 100515896 Closed 7-Jan-15 12-Jan-15 CAMP CONFIRMED 7-Jan-15 SYMPTOM_ONSET_DATE CT YES YES NO 8 7-Jan-15 YES NO YES 7-Jan-15 No 64 USA New Haven County MALE NO WHITE 1/9/2015 Campylobacter No 1/9/2015 2015 1 1.2 1.2 15_01 2 100516106 . . . . . . . 1/3/2015 Campylobacter No 1/3/2015 2015 1 1 1.1111 3 100516107 Closed 5-Jan-15 13-Jan-15 CAMP CONFIRMED 5-Jan-15 LAB_TEST_DATE CT NO YES NO . . NO YES YES . 58 USA Fairfield County FEMALE NO WHITE 1/5/2015 Campylobacter No 1/5/2015 2015 1 1 1.1 15_01 4 100516116 Closed 2-Jan-15 13-Jan-15 CAMP CONFIRMED 2-Jan-15 LAB_TEST_DATE CT NO . . UNKNOWN . 62 USA Fairfield County MALE NO WHITE 1/2/2015 Campylobacter Unknown 1/2/2015 2015 1 1 1 5 100516476 Closed 2-Jan-15 15-Jan-15 CAMP CONFIRMED 2-Jan-15 SYMPTOM_ONSET_DATE CT NO YES NO 5 2-Jan-15 am NO NO YES 2-Jan-15 am 56 USA New Haven County FEMALE NO BLACK_AFRICAN_AMERICAN 1/7/2015 Campylobacter No 1/7/2015 2015 1 1 1 15_01 6 100516481 Closed 5-Jan-15 15-Jan-15 CAMP CONFIRMED 5-Jan-15 SYMPTOM_ONSET_DATE CT NO YES NO 2 5-Jan-15 9:00 PM YES NO YES 5-Jan-15 31 USA New Haven County FEMALE NO WHITE 1/7/2015 Campylobacter Yes 1/7/2015 2015 1 1 1 15_01 7 100516484 Closed 8-Jan-15 15-Jan-15 CAMP CONFIRMED 8-Jan-15 LAB_TEST_DATE CT NO . . UNKNOWN . 57 USA New London County FEMALE UNKNOWN BLACK_AFRICAN_AMERICAN 1/8/2015 Campylobacter Unknown 1/8/2015 2015 1 1 1 15_01 8 100516485 Closed 4-Jan-15 15-Jan-15 CAMP CONFIRMED 4-Jan-15 SYMPTOM_ONSET_DATE CT YES YES NO 8 6-Jan-15 NO NO YES 4-Jan-15 61 USA New Haven County MALE NO WHITE 1/11/2015 Campylobacter No 1/11/2015 2015 1 1 1 15_02 9 100516544 Closed 7-Jan-15 16-Jan-15 CAMP CONFIRMED 7-Jan-15 LAB_TEST_DATE CT NO . . UNKNOWN . 94 USA Hartford County FEMALE UNKNOWN Unknown 1/7/2015 Campylobacter Unknown 1/7/2015 2015 1 1 1 15_01   ... View more

?Hi Paige, the code is not working quite right (I think), and my dataset has a lot of confidential fields. Would it work if I attached a few de-identified observations in excel format? ... View more

Thanks! Could you advise on calculating the average count variable? My dataset contains case counts for 2015-2019, so I would like to average the monthly counts across all years. ? ... View more