Diamond | Level 26

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

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

``````
--
Paige Miller
Fluorite | Level 6

## Re: Add average line to bar chart

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.

Diamond | Level 26

## Re: Add average line to bar chart

My mistake, try this

``````proc summary data=find_month nway;
class what_month;
var count;
output out=_mean_ mean=mean_count;
run;``````
--
Paige Miller
Discussion stats
• 17 replies
• 3519 views
• 1 like
• 3 in conversation