Proc SGPLOT for grouped barchart, UNgrouped line overlaid

[Abe]

Hi there,

I'm hoping someone on this board can assist me with finding a solution for my problem. What I'm trying to do is graph a grouped barchart with a line overlaid. My barchart is portfolio balances for both the bank I work for (Group=Bancorp) and for a peer group (Group=Peer); And the line I want to overlay is unemployment (U3 Unemployment).

I've tried making my dataset two ways.

In the first way, I simply add unemployment to my Bancorp/Peer portfolio balances dataset, al la...

Date	Grp	Balance	Unemployment
2011Q1	Peer	2000	8.8
2011Q1	Bancorp	1700	8.8

When I graph this (of course with more quarters of data following the same format) using the following statements, I get a grouped barchart with a line overlaid, but the legend reveals that SAS treats the unemployment number as being inextricably tied to the Peer and Bancorp--making a spot for Peer Unemployment and Bancorp Unemployment, which doesn't work...

proc sgplot data=Bancorp_Complete_with_Unemp;

    y2axis label="Unemployment Rate" min=0;

    yaxis label="Balances Outstanding x $1000";

    vbar date /group=grp groupdisplay=cluster response=balance legendlabel=" " name='Balances';

    vline date /y2axis response=U3_Unemp group=grp datalabel=U3_Unemp lineattrs=(color=red thickness=2) legendlabel="Unemployment" name='Unemp';

    xaxis display=all valueshint grid fitpolicy=rotate  interval=QUARTER label=" ";

    keylegend 'Balances' 'Unemp' / location=outside title="";

run;

The same block of code, run against a dataset of the following format also doesn't work...

Date	Grp	Balance	Unemployment
2011Q1	Peer	2000
2011Q1	Bancorp	1700
2011Q1	Unemployment		8.8

This dataset results in four entries in the legend: one for an Unemployment bar of zero height, one correct one for unemployment (actually displayed on the graph), and two for Peer and Bancorp which don't appear on the graph (because their unemployment numbers are missing).

Is there any way to graph this unassociated time series (Unemployment) over the top of my grouped barchart using SGPLOT?

And if not what is the easiest alternative? I really like the simplicity of the SGPLOT construction but am frustrated by its lack of flexibility. Also, attempting to combine the VBAR statement with a SERIES statement throws an error which informs me I cannot mix incompatible chart types.

Please help. Thank you!

[ballardw]

It sounds like you don't want to include 'Unemp' In the KEYLEGEND statement, probably with the first data layout. That should remove the legend entries for unemployment.

[Jay54]

You have multiple options:

1.  If you can presummarize your data (with one observation per category/group combination), then you can use the VBARPARM statement which expects summarized data.  You can overlay a SERIES plot on a VBARPARM.  Now, you can provide a LEGENDLABEL to the SERIES plot and include that in the legend to get the entries you want.  Or, you can still use curvelabel to directly label the series plot.

2.  If you don't want to presummarize the data yourself, you can use GTL to overlay a SERIESPLOT on top of a BARCHART statement.  Run SGPLOT with TMPLOUT='filename' to get the GTL created by SGPLOT.  Then, replace the VLINE by a SERIESPLOT, and drop the GROUP role.

3.  Lastly, use ODS Graphics Designer from the TOOLS menu.  You can drag and drop your way to the graph you need. 

Do VIEW->CODE to see the GTL code.

See related blog post at:  http://blogs.sas.com/content/graphicallyspeaking/