Solved
Contributor
Posts: 29

# Extra vertical bar for total/overall via PROC GCHART

[ Edited ]

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?

Accepted Solutions
Solution
‎07-26-2017 11:16 PM
Super User
Posts: 13,523

## Re: Extra vertical bar for total/overall via PROC GCHART

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.

All Replies
Super User
Posts: 9,599

## Re: Extra vertical bar for total/overall via PROC GCHART

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

Solution
‎07-26-2017 11:16 PM
Super User
Posts: 13,523

## Re: Extra vertical bar for total/overall via PROC GCHART

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.

Contributor
Posts: 29

## Re: Extra vertical bar for total/overall via PROC GCHART

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

☑ This topic is solved.