topic Re: [SUSPECTED SPAM] Re: Add average line to bar chart in Graphics Programming

Add average line to bar chart

jbrock — Fri, 03 Jan 2020 16:29:51 GMT

Hello!

I have a large case dataset. I would like to create a barchart showing case counts by month for 2019 (y-axis is frequency), with an average line overlayed on top. The line would represent the 5-year average case count by month. I am using the following code to create the barchart:

ods html file="P:\SASDATA\DescriptiveStats" file="campy.html";
proc gchart data=work.camp;
vbar datamonth/ discrete type=freq;
where datayear=2019;
run;
quit;
ods html close;

How can I create and add a 5-year average line to this barchart? Thanks for any thoughts!

Jessica

Re: Add average line to bar chart

PaigeMiller — Fri, 03 Jan 2020 17:41:45 GMT

You have to calculate the 5 year average before you draw the plot, then store the value in a macro variable. Then use the REF= option of the VBAR statement.

Re: Add average line to bar chart

jbrock — Fri, 03 Jan 2020 17:48:49 GMT

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.

?

Re: Add average line to bar chart

PaigeMiller — Fri, 03 Jan 2020 17:58:25 GMT

Provide us with a portion of the actual data as SAS data step code, following these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Add average line to bar chart

jbrock — Fri, 03 Jan 2020 19:33:49 GMT

?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?

Re: Add average line to bar chart

PaigeMiller — Fri, 03 Jan 2020 20:07:49 GMT

Many people (including me) will not download Excel files because they are a security risk.

You can type or paste the de-identified data into your reply, click on the {i} icon and then type or paste the data into that window.

Re: Add average line to bar chart

jbrock — Mon, 06 Jan 2020 12:42:16 GMT

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

100515896

Closed

7-Jan-15

12-Jan-15

CAMP

CONFIRMED

7-Jan-15

SYMPTOM_ONSET_DATE

YES

7-Jan-15

YES

7-Jan-15

USA

New Haven County

MALE

WHITE

1/9/2015

Campylobacter

1/9/2015

2015

1.2

15_01

100516106

1/3/2015

Campylobacter

1/3/2015

2015

1.1111

100516107

Closed

5-Jan-15

13-Jan-15

CAMP

CONFIRMED

5-Jan-15

LAB_TEST_DATE

YES

USA

Fairfield County

FEMALE

WHITE

1/5/2015

Campylobacter

1/5/2015

2015

1.1

15_01

100516116

Closed

2-Jan-15

13-Jan-15

CAMP

CONFIRMED

2-Jan-15

LAB_TEST_DATE

UNKNOWN

USA

Fairfield County

MALE

WHITE

1/2/2015

Campylobacter

Unknown

1/2/2015

2015

100516476

Closed

2-Jan-15

15-Jan-15

CAMP

CONFIRMED

2-Jan-15

SYMPTOM_ONSET_DATE

YES

2-Jan-15

YES

2-Jan-15

USA

New Haven County

FEMALE

BLACK_AFRICAN_AMERICAN

1/7/2015

Campylobacter

1/7/2015

2015

15_01

100516481

Closed

5-Jan-15

15-Jan-15

CAMP

CONFIRMED

5-Jan-15

SYMPTOM_ONSET_DATE

YES

5-Jan-15

9:00 PM

YES

5-Jan-15

USA

New Haven County

FEMALE

WHITE

1/7/2015

Campylobacter

Yes

1/7/2015

2015

15_01

100516484

Closed

8-Jan-15

15-Jan-15

CAMP

CONFIRMED

8-Jan-15

LAB_TEST_DATE

UNKNOWN

USA

New London County

FEMALE

UNKNOWN

BLACK_AFRICAN_AMERICAN

1/8/2015

Campylobacter

Unknown

1/8/2015

2015

15_01

100516485

Closed

4-Jan-15

15-Jan-15

CAMP

CONFIRMED

4-Jan-15

SYMPTOM_ONSET_DATE

YES

6-Jan-15

YES

4-Jan-15

USA

New Haven County

MALE

WHITE

1/11/2015

Campylobacter

1/11/2015

2015

15_02

100516544

Closed

7-Jan-15

16-Jan-15

CAMP

CONFIRMED

7-Jan-15

LAB_TEST_DATE

UNKNOWN

USA

Hartford County

FEMALE

UNKNOWN

Unknown

1/7/2015

Campylobacter

Unknown

1/7/2015

2015

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

Re: Add average line to bar chart

PaigeMiller — Mon, 06 Jan 2020 13:18:45 GMT

So as I understand things, you want to count cases in each month. All of these would count as cases in January 2015 except row 2?

Is it possible to have event_date different month from create_date different month from effective_From_date? In this case, which date do we use to determine counts in a month?

Re: Add average line to bar chart

jbrock — Mon, 06 Jan 2020 13:30:49 GMT

?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.

Re: Add average line to bar chart

PaigeMiller — Mon, 06 Jan 2020 13:55:44 GMT

UNTESTED CODE

/* First obtain counts by month */
proc freq data=have;
     table dtspec2/noprint out=_counts_;
     format dtspec2  yymm6.;
run;

/* Next get average of 5 years */
proc summary data=_counts_ (where=(year(dtspec2)>=2015 and year(dtspec2)<=2019));
    var count;
    output out=_mean_ mean=mean_count;
run;

/* Create macro variable */
data _null_;
    set _mean_;
    call symputx('mean_count',mean_count);
run;

/* Create plot */
proc gchart data=work.camp;
vbar datamonth/ discrete type=freq ref=&mean_count;
where datayear=2019;
run;
quit;

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

jbrock — Mon, 06 Jan 2020 17:29:49 GMT

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.

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

PaigeMiller — Mon, 06 Jan 2020 17:54:44 GMT

Going back to the original problem statement

I would like to create a barchart showing case counts by month for 2019 (y-axis is frequency), with an average line overlayed on top. The line would represent the 5-year average case count by month.

This seems to refer to a single average and a single line, the word line does not appear as the plural word "lines". Now you are asking for different lines for each month? One line for January that just goes over the different January bars, and similarly a separate line for February and all the other months?

This would require a somewhat different approach, and although it would be possible in PROC GPLOT, you would have to use the ANNOTATE facility, and I no longer remember those details. Nor is it immediately obvious how to do this in the newer PROC SGPLOT. So at this time, I do not have an answer.

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

DanH_sas — Mon, 06 Jan 2020 18:16:22 GMT

Doing this in SGPLOT, I would overlay a STEP plot on top of a VBARPARM.

Re: Add average line to bar chart

PaigeMiller — Tue, 07 Jan 2020 13:29:04 GMT

Thinking outside the box, it would be relatively simple to produce dots for the average of each month over the last five years (one dot for the value in January, a different value for the dot for February, etc.) on the vertical bar plot, and then connect the dots, so the plot shows the bars and the sequence of dots. Even better yet, in my opinion, instead of bars for the monthly counts, you have one line (let's say red) that shows the counts by month, and another line (let's say blue) that shows the average counts in each month. This would visually get the information across and again in my opinion, would be a better plot layout than bars plus dots, or bars plus lines.

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

jbrock — Tue, 07 Jan 2020 13:34:49 GMT

?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?

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

PaigeMiller — Tue, 07 Jan 2020 13:48:24 GMT

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

Re: Add average line to bar chart

jbrock — Fri, 10 Jan 2020 14:19:35 GMT

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.

Re: Add average line to bar chart

PaigeMiller — Fri, 10 Jan 2020 14:26:07 GMT

My mistake, try this

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