Grouped Bar Chart, Mean line

[monica1221]

Hello!

I am trying to produce a graph like below:

The line should connect the mean amongst the groups at each Study Week.

I understand how to do multiple lines connecting each group across Study Weeks, but how can I produce one line to connect the means for each Study Week?

This is my code (which does not work):

proc sgplot data=alldat0&testcd ;
  
by subject;
vbar week/
 group=vstptnumm
 groupdisplay = CLUSTER 
 response=vsstresn 
 ;

 vline week/response=vsmean y2axis;
 
 
 format vstptnumm $vstim.;

run;

Vline needs to have the group= option as well. 

Any advice? Thanks so much!

Monica

[DanH_sas · Accepted solution]

Currently, there are restrictions that prevent VLINE from working for you in this scenario. However, there is a solution. You can precompute the values in PROC MEAN and use VBARPARM and SERIES to display the result. Here is an example:

proc means data=sashelp.cars nway;
class origin type;
var mpg_city;
output out=bardata mean=;
run;

proc means data=sashelp.cars nway;
class origin;
var mpg_city;
output out=linedata mean=;
run;

data merged;
set bardata linedata (keep=origin mpg_city rename=(origin=origin2 mpg_city=mpg_city2));
run;

proc sgplot data=merged;
vbarparm category=origin response=mpg_city / group=type groupdisplay=cluster;
series x=origin2 y=mpg_city2;
run;

I'm not sure of your SAS version; so, if GROUPDISPLAY gives you an error, just remove it and you should still get clustered bars (this default changed when the option was added).

Hope this helps!

Dan

[monica1221]

Hi Dan,

I have a follow up now. How can I add a vertical reference line to this chart? I am trying but nothing is showing up.

This is my code:

proc sgplot data=alldat2&testcd dattrmap=attrmap;

by subject;

vbarparm category=vsdy response=vsstresn / group=vstptnumm groupdisplay=cluster name="bar" attrid=X;
series x=vsdy y=vsmean / lineattrs=(thickness=1) y2axis name="line";

refline 100/axis=x;

yaxis VALUES= (&&min&testcd to 166 by 10) ;
y2axis VALUES=(&&min&testcd to 166 by 10);

format vstptnumm $vstim. vsdy week.;;

run;

[DanH_sas]

This ie actually a little tricky, due to the discrete axis and the format. The REFLINE statement knows nothing about the format associated with the "vsdy" variable, so the "100" gets plotted on the end of the discrete axis. Since there is no real data there, the refline gets clipped off of the end (using the NOCLIP option on the REFLINE statement would probably make it appear). However, your desire is to have the line put at week 100, so you need to put that value in a data set where you can associate a format with it. Given the example I gave you earlier, this is probably the simplest way:

data merged;
set bardata linedata (keep=origin mpg_city rename=(origin=origin2 mpg_city=mpg_city2)) end=_last_;
if (_last_) then refvar=100;
run;

Now, on your FORMAT statement, add the refvar:

format vstptnumm $vstim. vsdy week. refvar week.;

and add the variable to the REFLINE statement:

refline refvar / axis=x;

Give that a try and see if it works for you.

Thanks!

Dan

[ANWZimmerman]

Thank you, this was really helpful to me, except I have one issue.

When I run the code you provided on sashelp.cars, I get a block in the legend for the missing rows.

How can  I eliminate that?

[DanH_sas]

What version of SAS are you running?

[ANWZimmerman]

9.4M5

[DanH_sas]

The artificial missing value for the group variable is created due to merging data sets of different lengths. You can working around this issue by assigning a "type" when the value is missing while doing the merging:

data merged;
set bardata linedata (keep=origin mpg_city rename=(origin=origin2 mpg_city=mpg_city2));
if type = " " then type="Wagon";
run;

Let me know if this works for your situation.

Thanks!
Dan

[ANWZimmerman]

I suspect my challenge will be that I'll then have two rows that duplicate. My X var is time, I'm using stacked bars, but they want a line to connect the top of the bars, basically total.
I was trying to not have the total merged in, really just want to have one set of totals so it's only one line (some of the groups for the stacked bars don't run the whole series, and I've got one that skips which is causing issues because that line just connects the existing points and doesn't reflect the true series)

I'll just go back to merging it in and manually skip merging it into the inconsistent series.

[ANWZimmerman]

Well, not the worst solution. I do get an warning, but it doesn't matter and gets the graph I want.

WARNING: The data for a BARCHARTPARM statement are not appropriate. The BARCHARTPARM statement expects summarized data. The bar chart might not be drawn correctly.

[monica1221]

Thank you, this worked!