GTL waterfall chart aligning count and percent y-axes

[cdorger]

Hi everybody,

I want to create a waterfall chart that shows how counts drop as you move through the category plotted on the x-axis.  But it would also be nice to see percentages on a second y-axis at the right.  I have been able to jury-rig this somewhat by overlaying 2 waterfallchart plots where the first one draws no bars and then using offsetmax= option for the y2axis.  But getting the 100% tick to line up exactly with the total number of counts has been tricky and takes some trial and error.  Is there a way to do this in the manner of columndatarange=unionall option in the layout lattice statement?

***Open SASHELP dataset;
data cars;
    set sashelp.cars;
run;

***count number of vehicles, store in macro var;
proc sql;
	select count(*) AS NCars INTO :IBV
	from cars;
quit;

***count cars by Vehicle type;
proc freq data=cars;
	tables type / out=vtype(keep=type count percent rename=(type=vehtype));
run;

***Sort from greatest to least ;
proc sort data=vtype;
	by descending count;
run;

***Create numeric plotting xvar and format data for formatting discrete x axis;
data vtype2(keep=x vehtype count percent)
	f1(keep=start label fmtname TYPE);
	set vtype end=lastline;
	retain fmtname 'discax' TYPE 'N';
	x=_N_;
	count=-count;
	percent=-percent;
	start=x;
	label=vehtype;
	if not lastline then do;
		output vtype2;
		output f1;
	end;
run;

***Look at data for plot;
proc print data=vtype2;
run;

***Create format for X-axis;
proc format cntlin=f1;
run;

***Figure out what max tick value on y1 axis should be;
%let ymax=450;
***Compute offsetmax for Percent axis (Y2axis);
data _NULL_;
	call symput('OSM',put(&ymax/&totcars - 1,best4.));
run;

%put *********&OSM*************;

***Create template for overlaying waterfall chart with numbers on left axis and percent on right;
proc template;
	define statgraph wfal1;
	nmvar MAXY "max y axis value" OSM "OffSet Max" IBV "Init Bar Value";
		begingraph;
		entrytitle 'Waterfall of Models by Vehicle Type';
			layout overlay / xaxisopts=(label="Restriction"
										discreteopts=(
										tickvaluefitpolicy=split
										tickvaluesplitchar="*"
											)
										)
							 yaxisopts=(label="Number of Vehicles"
										offsetmax=0
							 			linearopts=(
													viewmin=0 viewmax=MAXY
													)
										)
							y2axisopts=(label="Percent of Vehicles"
										griddisplay=on
										offsetmax=.05
										linearopts=(viewmin=0 viewmax=100)
										)
				; *end LAYOUT OVERLAY stmt;
				*Percent on right side;
				waterfallchart category=x response=percent / yaxis=y2
														  finalbartickvalue=""
														  initialbartickvalue=""
														  datatransparency=1
														  initialbarvalue=100
														  stat=sum
														  ;
				*numbers on left side;
				waterfallchart category=x response=count / yaxis=y
														  finalbartickvalue="All Other"
														  initialbartickvalue="All Vehicles"
														  initialbarvalue=IBV
														  barlabel=TRUE
														  fillattrs=(color=GRAYC9)
														  initialbarattrs=(color=GRAYC9)
														  finalbarattrs=(color=GRAYC9)
														  stat=sum
														  ;
			endlayout;
		endgraph;
	end; *define;
run;

%let MAXY=450;
*** *** *** *** *** *** *** *** *** Options for HTML output *** *** *** *** *** *** *** *** *** *** *** ;
options orientation=landscape topmargin=0.5in bottommargin=0.5in leftmargin=0.5in rightmargin=0.5in;
ods _all_ close;
ods noproctitle;
ods graphics on /
			border=off
			height=600px
			width=800px
			reset=index(1) /* causes SAS to overwrite output images */
			;

ods html body="waterFallTest.html"
		path="<path>/SAS_output/"(url=none)
		;

	proc sgrender data=vtype2 template=wfal1;
		format x discax.;
	run;

ods html close;
ods graphics off;
ods listing;
options orientation=portrait;



Quit;
********************************************** END PROGRAM ************************************************;

[JeffMeyers]

Hello,

   All you need to do is set the maximum of the y axis to be the total number of cars, the maximum of the y2 axis to 100, and to set the maximum offset of both to be the same (e.g. 0.05).

Here's my mod of your code.  I update your MAXY to be your IBV macro variable and set your offsetmax to both be 0.05.

ods path WORK.TEMPLAT(UPDATE) SASHELP.TMPLMST (READ);
***Create template for overlaying waterfall chart with numbers on left axis and percent on right;
proc template;
    define statgraph wfal1;
    nmvar MAXY "max y axis value" OSM "OffSet Max" IBV "Init Bar Value";
        begingraph;
        entrytitle 'Waterfall of Models by Vehicle Type';
            layout overlay / xaxisopts=(label="Restriction"
                                        discreteopts=(
                                        tickvaluefitpolicy=split
                                        tickvaluesplitchar="*"
                                            )
                                        )
                             yaxisopts=(label="Number of Vehicles"
                                        offsetmax=0.05
                                        linearopts=(
                                                    viewmin=0 viewmax=MAXY
                                                    )
                                        )
                            y2axisopts=(label="Percent of Vehicles"
                                        offsetmax=0.05
                                        griddisplay=on
                                        linearopts=(viewmin=0 viewmax=100)
                                        )
                ; *end LAYOUT OVERLAY stmt;
                *Percent on right side;
                waterfallchart category=x response=percent / yaxis=y2
                                                          finalbartickvalue=""
                                                          initialbartickvalue=""
                                                          datatransparency=1
                                                          initialbarvalue=100
                                                          stat=sum
                                                          ;
                *numbers on left side;
                waterfallchart category=x response=count / yaxis=y
                                                          finalbartickvalue="All Other"
                                                          initialbartickvalue="All Vehicles"
                                                          initialbarvalue=IBV
                                                          barlabel=TRUE
                                                          fillattrs=(color=GRAYC9)
                                                          initialbarattrs=(color=GRAYC9)
                                                          finalbarattrs=(color=GRAYC9)
                                                          stat=sum
                                                          ;
            endlayout;
        endgraph;
    end; *define;
run;

%let MAXY=&ibv;
ods graphics on /
            border=off
            height=600px
            width=800px
            reset=index(1) /* causes SAS to overwrite output images */
            ;

    proc sgrender data=vtype2 template=wfal1;
        format x discax.;
    run;

[cdorger · Accepted solution]

Jeff,

your solution worked great...until I tried it on the data for which I am creating the plot.  These counts are 2 orders of magnitude greater than those in the cars dataset and I had the problem where the next major tick mark would show up and the height of the initial bar would not line up with the 100% tick.  This seems to happen if the initial bar height is more than about 70% of the way from the last major tick value.  I made a small modification to the first 3 steps of the program I posted so you (or anyone else) can see the effect of increasing the counts. (See the SAS code below.)  A multiplier value of 111 increases the counts from 428 to 47508 and a 50,000 tick value is put on the y-axis.

But...I discovered the THRESHOLDMAX= option for LINEAROPTS=() and setting that to 0 suppresses the last major tick value so your post is ACCEPTED AS A SOLUTION!  Thanks!

***Increase values of counts with arbitrary multiplier for testing different ranges on graph;
%let multiplier=111;

***Open SASHELP dataset;
data cars;
	set sashelp.cars;
	retain w &multiplier;
run;

***count number of vehicles, store in macro var;
proc sql;
	select &multiplier*count(*) AS NCars INTO :IBV
	from cars;
quit;

***count cars by Vehicle type;
proc freq data=cars;
	tables type / out=vtype(keep=type count percent rename=(type=vehtype));
	weight w;
run;