[SUSPECTED SPAM] Re: Add average line to bar chart

[PaigeMiller]

/* Compute counts for each month */
proc freq data=have;
     table dtspec2/noprint out=_counts_;
     format dtspec2  yymm6.;
run;

data find_month;
    set _counts_(where=(year(dtspec2)>=2015 and year(dtspec2)<=2019));
    what_month=month(dtspec2);
run;

/* Next get mean for each month */
proc summary data=find_month nway;
    var what_month;
    output out=_mean_ mean=mean_count;
run;

/* 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;

/* Create plot */
proc sgplot data=_counts1_;
    series x=dtspec2 y=count/markerattrs=(color=red marker=dot) lineattrs=(color=red pattern=solid);
    series x=what_month y=mean_count/markerattrs=(color=blue marker=dot)
lineattrs=(color=blue pattern=dash);
run;

[jbrock]

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.

[PaigeMiller]

My mistake, try this

proc summary data=find_month nway;
    class what_month;
    var count;
    output out=_mean_ mean=mean_count;
run;