HBARs with left justified tick labels

[EdB]

Hello

I'm trying to replicate a PDF (from Excel) report in SAS 9.2 and I haven't found the best solution although there seem to be many possible approaches.

The single page PDF report contains 3 small tables and a table with data and horizontal bars with percentages. The tables / reports are no problem but I haven't figured out how to create a table with horizontal bars that looks good. I found an example in a link posed by Sanjay in the beginning of this forum which has almost what I'm trying to get - a table of statistics with hbars with golf data.

The structure of the example has columns with age, events, wins, player's name, and earnings in a hbar chart. My data are similar but the player's name column needs to be the left most column and left justified. It seems that there are no options to change it from right justified. This looks like the conclusion in the thread "SGPLOT: Wrap & Left Justify Tickmark Values" from Oct 8th.

One workaround is to use a monospace font and make the names the same length. This aligns the data to the left, but it doesn't look very good.

I also thought of an alternative based on traffic lighting examples. If it's possible to control the formatting of a cell, I shoulld be able to create a table template if I can find a character format to simulate bars.

Can anyone think of a better way or is the only alternative to send the data to Excel and format it there?

Thanks for any advice
Ed

[GraphGuy]

Hmm ... this forum _really_ needs a way to post up attachments, eh!?!

[Cynthia_sas]

I'm confused...you want something like what's produced by the program in this posting, only an HBAR instead of VBAR???
http://support.sas.com/forums/thread.jspa?messageID=26551枷

cynthia

[EdB]

Hi,
I want this with left justified names.
Ed

http://support.sas.com/kb/35/135.html

[GraphGuy]

EdB,

The example you point to uses the new "proc template" and "proc sgrender".
I'm not an expert at that, so I'm not sure if you can control the justification there.

But I have re-written the example, using traditional "old school" SAS/Graph
proc gchart, with the table programmatically annotated to the side, and
you can easily control the justification of the player names (ie, bar labels)
using the axis statement:

axis1 label=none value=(justify=left);

Here's the complete code for my re-write:

[pre]
data PGA2007;
input Rank 2. Player & $15. Age Events Rounds CutsMade Top10 Wins Earnings;
retain Constant 1;
label CutsMade="Cuts Made" Top10="Top 10s";
format Earnings dollar12.;
format age events wins 3.0;
datalines;
1 Tiger Woods 33 16 61 16 12 7 10867052
2 Phil Mickelson 38 22 73 16 7 3 5819988
3 Vijay Singh 45 27 101 25 7 2 4728377
4 Steve Stricker 41 23 80 19 9 1 4663077
5 K.J. Choi 38 25 88 20 7 2 4587859
6 Rory Sabbatini 32 23 80 18 10 1 4550040
7 Jim Furyk 38 24 84 20 8 1 4154046
8 Zach Johnson 32 23 78 18 5 2 3922338
9 Sergio Garcia 29 19 67 16 7 0 3721185
10 Aaron Baddeley 27 23 82 19 7 1 3441119
run;

data my_anno; set PGA2007;
length text $50 style $20;
function='label'; style='"arial"'; hsys='3'; when='a';
ysys='2'; midpoint=Player;
/* annotate totals at end of bars */
xsys='2'; position='6';
text=' '||trim(left(put(Earnings,dollar12.)));
output;
/* annotate the values for the table to the left of the bar chart */
xsys='3'; position='5';
x=5; text=trim(left(Age)); output;
x=12; text=trim(left(Events)); output;
x=19; text=trim(left(Wins)); output;
run;

data headings;
length text $50 style $20;
function='label'; style='"arial/bold"'; hsys='3'; when='a';
/* annotate the headings for the table to the left of the bar chart */
xsys='3'; position='5';
ysys='3'; y=90;
x=5; text="Age"; output;
x=12; text="Events"; output;
x=19; text="Wins"; output;
run;

/* combine all your anno stuff */
data my_anno; set my_anno headings;
run;

goptions ftitle="arial/bold" ftext="arial" gunit=pct htitle=4 htext=2.25;

/* control left/right/center justification of Player name here! */
axis1 label=none value=(justify=left);

axis2 label=(font="arial/bold" "Earnings") minor=none offset=(0,0);

title1 "Professional Golf Statistics for 2007";
/* fake/blank title, to add white space on the left (don't try it with dev=java or activex!) */
title2 angle=90 height=30pct " ";

pattern1 v=s c=cx9BC4E2;

proc gchart data=PGA2007 anno=my_anno;
hbar Player / type=sum sumvar=Earnings descending nostats
maxis=axis1
raxis=axis2
autoref cref=graydd clipref
;
run;
[/pre] added 'pre' tags around code, as recommended by Peter C.

Message was edited by: Robert Allison @ SAS

[Peter_C]

very impressive implementation.
but beware, ....
when test running code copied from the message, "age" is pulled into the player name. The conflict was between forum message handling (equivalent of left(compbl(code)) ) and the format modifier & [accepting a single blank within a string, requires double blank to terminate string]. In fact more than age might be pulled into name, as the 15 characters starting at the beginning of the name will be swallowed as "Player"
recommendation (1) post unalterable code within [pre [/pre tags or (2) ensure strings that require the "&" input modifier appear last, or (3) lobby for a [code tag that more easily pastes into SAS (not needing to be pasted into wordpad first) Message was edited by: Peter.C

[EdB]

Hi:

I just wanted to follow up on this. I saw the notes on using annotate - forgot about this approach entirely. My opinion is that you can do almost anything with it but it's more complex and requires different skills and more training.

The GTL workarounds seem to get close to my goal of left justifying a label.

I began with the golf example http://support.sas.com/kb/35/135.html
and made some minor tweaks - the major one was adding another scatterchart with only a y2axis displayed.

The one remaining issue is that the cell header for the first column doesn't display so I'm not done, but it's getting closer.

Thanks for the feedback and if anyone has ideas on the cell header I'd sure like to hear them.
Ed


The code (with some minor tweaks).

data PGA2007;
input Rank 2. Player & $15. Age Events Rounds CutsMade Top10 Mistresses Earnings;
retain Constant 1;
format Earnings dollar12.;
format age events Mistresses 3.0;
datalines;
1 Tiger Woods 33 16 61 16 12 9 10867052
2 Phil Mickelson 38 22 73 16 7 0 5819988
3 Vijay Singh 45 27 101 25 7 0 4728377
4 Steve Stricker 41 23 80 19 9 0 4663077
5 K.J. Choi 38 25 88 20 7 0 4587859
6 Rory Sabbatini 32 23 80 18 10 0 4550040
7 Jim Furyk 38 24 84 20 8 0 4154046
8 Zach Johnson 32 23 78 18 5 0 3922338
9 Sergio Garcia 29 19 67 16 7 0 3721185
10 Aaron Baddeley 27 23 82 19 7 0 3441119
run;

%let standardopts=%str( walldisplay=none border=false
yaxisopts=(display=none reverse=true type=discrete)
xaxisopts=(display=none offsetmin=0.1 offsetmax=0) );
* options for all scatterplots;

proc template;
define statgraph BarTableHorz;
begingraph / designwidth=600px designheight=400px;
entrytitle "Professional Golf Statistics for 2007";
layout lattice / columns=5 columngutter=1
columnweights=(.20 .12 .12 .14 .42);
cell; /* column1: name */
cellheader; entry halign=right "Player"; endcellheader;
layout overlay / &standardopts. y2axisopts=(reverse=true type=discrete);
scatterplot y=player x=constant / markercharacter=Player
markerattrs=(size=0) yaxis=y2;
endlayout;
endcell;
cell; /* data column */
cellheader; entry halign=center "Age" ; endcellheader;
layout overlay / &standardopts. ;
scatterplot y=player x=constant / markercharacter=age
markerattrs=(size=0);
endlayout;
endcell;
cell; /* data column */
cellheader; entry halign=center "Events"; endcellheader;
layout overlay / &standardopts. ;
scatterplot y=player x=constant / markercharacter=events
markerattrs=(size=0);
endlayout;
endcell;
cell; /* data column */
cellheader; entry halign=center "Mistresses"; endcellheader;
layout overlay / &standardopts. ;
scatterplot y=player x=constant / markercharacter=Mistresses
markerattrs=(size=0);
endlayout;
endcell;
cell; /* barchart */
cellheader; entry halign=center "Earnings" ; endcellheader;
layout overlay / walldisplay=none border=false
yaxisopts=(reverse=true label=" " display=none)
xaxisopts=(griddisplay=on labelattrs=(weight=bold)
linearopts=(tickvalueformat=(extractscale=true)) );
barchart x=player y=Earnings /orient=horizontal outlineattrs=(color=white);
endlayout;
endcell;
endlayout;
endgraph;
end;
run;

proc template;
define Style BarTableHorzStyle;
parent = styles.journal;
style GraphFonts from GraphFonts / 'GraphDataFont' = (", ",10pt);
style graphaxislines from graphaxislines / contrastcolor=white;
/* keep axis but hide it. it needs to exist to or the labels are centered */
end;
run;

ods pdf file="c:\temp\BarTableHorz4.pdf" style=BarTableHorzStyle;
title;
proc sgrender data=PGA2007 template=BarTableHorz;
run;
ods pdf close;

[GraphGuy]

A little follow-up on the annotate solution -- for those of you that don't want to run the code to see what the graph looks like, here's a link to the output on an easy-to-view webpage:

http://robslink.com/SAS/democd42/bar_table.htm

And a link to the sas code:

http://robslink.com/SAS/democd42/bar_table_info.htm