Extra vertical bar for total/overall via PROC GCHART

[BigPete]

Pardon me if it may have been already addressed elsewhere. Suppose you're given a dataset with list of quarterbacks' names, passes made, and passes completed.

data feeder;
input QB $ completed passes;
datalines
Brett 23 35
Tom 21 29
Peyton 34 41
Eli 32 37
Aaron 26 38
;
run;

proc sql;
Create table QB_pass_rate as select
  *,
  completed/passes as completion_rate
from feeder;

Insert into QB_pass_rate as select
  'Total' as QB,
  sum(completed) as completed,
  sum(passes) as passes,
  sum(completed)/sum(passes) as completion_rate
from feeder;
quit;

axis1 label = (a=90 f= "Arial/bold"  "Completion Rate (%)");
axis2 label = (f= "Calibri/bold" "QBs")
      order=('Total' 'Aaron' 'Brett' 'Eli' 'Peyton' 'Tom');

proc gchart data = qb_pass_rate;
vbar qb /  outside = sum
	raxis = axis1
	maxis= axis2
	coutline=black
	woutline=2;

format completion_rate percent8.2;
run;
quit;

Now the client wants a bar chart with names vs. completion rate and an extra bar for overall/total and she showed me the Excel approach if you like to see the attachment for reference. She wants the same kind of bar chart via SAS where the total bar has its own color standing out from the rest. Like Excel, is there a way to do the same in EG by righ-clicking on a specific bar to change that particular color or whatever? Maybe throw in PATTERN statements in base SAS?

[RW9]

Sorry, that code is hard to read as its all on one line and Excel files will not be downloaded (security risk).  

What I would suggest, as always is to move to using sgplot and graph template language - far more flexible and easy to use.  With that you can overlay graphs as much as you like.  A great resource for this is:

http://blogs.sas.com/content/graphicallyspeaking/

In your case its just a matter of getting the data together, i.e. have one dataset with all the categories and results, and then plotting them with a simple bar chart (note make your categories numeric so they order correctly and then apply a format so they show as you want).

[ballardw · Accepted solution]

Here's one approach with SGPLOT.

data feeder;
input QB $ completed passes;
group=1;
datalines;
Brett 23 35
Tom 21 29
Peyton 34 41
Eli 32 37
Aaron 26 38
;
run;

/* at this point main this is basically adding the _type_
   variable but would be appropriate if your data was 
   per game or similar
   A GROUP variable creates a stacked barchart with 
   separate colors for each level. If you only have
   on value for a group it stands out as desired.
*/
proc summary data=feeder;
class QB;
var completed passes;
output out=feedsum sum=;
run;

data qb_pass_rate;
   set feedsum;
   completion_rate = completed/passes ;
   if missing(QB) then QB='Total';
run;


proc sgplot data= qb_pass_rate noautolegend;
   vbar qb / response= completion_rate group=_type_;
run;

The appearance options for axis and such are significantly different that Gchart and vary with SAS release as there have been many changes to these procedures. You can research those.

This does have a potential order concern because the columns are sorting by QB name. Assigning a numeric value with a format is one way to order the bars. With later versions of SAS the option categoryorder in vbar statement can order the bars in descending or ascending order of the value of the response variable such as with:

vbar qb / response= completion_rate group=_type_

categoryorder=respdesc;

which still shows the total in a different color but you can see which are above/below the average for the group easily.

[BigPete]

Thanks! Much shorter and sweeter than the PROC SORT + PROC SQL INSERT approach.