SAS "Double" Waterfall sgplot

Question

I have a data set that looks like this:

data have;
    input category $ type $ percent order;
datalines;
X Full 1 1
X B -.04 .
X A -.02 .
X E 0 .
X D .03 .
X C .01 .
;
run;

I want to create a waterfall showing what is "Leftover" after the modifications A-E. The code below does this successfully.

I have two questions:

I wish to further waterfall from the "Leftover" bar. Suppose another modification, F, has a value of .60. I wish to subtract this value from "Leftover" to get "Leftover 2" (see picture http://tinypic.com/r/mb4tat/8 ). I don't know if this is possible, but I really would like both Leftovers displayed.
The code below is messy. I had to create some workarounds to get the bars to show up in the order I wanted. Is there a better way to dictate these values?

Thanks!

proc sort data=have(where=(percent<0)) 
    out=neg;
    by percent;
run;

data neg;
    set neg;
    order=_N_+1;
run;

proc sql noprint;
    select max(order) into :max_order from neg;
quit;

proc sort data=have(where=(percent>0 and type not in ("Full"))) 
    out=pos;
    by descending percent;
run;

data pos;
    set pos;
    order=_N_+&max_order.;
run;

data x_have;
    set have(where=(type="Full")) neg pos;
run;

proc sort data=x_have;
by order;
run;

proc sgplot data=have(where=(percent ne 0)) noautolegend;
waterfall category=type response=percent  / colorgroup=type dataskin=sheen datalabel name='a'
finalbartickvalue='Leftover';
;
xaxis display=(nolabel);
yaxis grid display=(nolabel) offsetmin=0;
run;

I might create two new rows with CATEGORY=LEFT_ONE and CATEGORY=LEFT_TWO or something... would that be close enough? It wouldn't be in the right margin, it would just be the last two. Otherwise, would you be okay with SGPANEL perhaps doing the LEFT_ONE and LEFT_TWO in a second panel? — Joe
Or possibly create a new variable that stores the values for the LEFT_ONE and LEFT_TWO bars, and do a second plot on top of the first plot for that (if WATERFALL allows that), which you can have slightly different formatting for. I'd also peruse Graphically Speaking and see if any of the stuff there is helpful for this - I know Sanjay has done some cool things that combine different plots in a way sort of like this, but I can't remember specifically. — Joe
Ideally, I'd like it to be in the same panel...it's really two waterfalls in the same panel; one results in 'Leftovers' and ignores F, the second one waterfalls F on 'Leftovers' to get 'Leftovers2'. I'll look at the blog you cited. I also saw some people who used proc template/sgpanel to create a waterfall...I wonder if the modifications can be made this way. — pyll
Also, I wouldn't need those two columns to be in the margin, that's just my crude rendition of what I need. The last two would be fantastic. I wonder what you mean by creating the two rows LEFT_ONE and LEFT_TWO? Are these summary rows? — pyll
I'm actually thinking my second suggestion - a second waterfall chart - might be the better option, but I'm not sure I am familiar with waterfall charts sufficiently to do this off the top of my head, and too busy to make it myself. — Joe

pyll pyll · Accepted Answer · 2015-04-09T02:54:40

Thanks to @Joe and Sanjay Matange, I started working this through a highlow plot instead of waterfall. It was a little messy to manipulate the data so it was repeatable (and I can probably go back and clean it up some), but here is the solution for anybody interested.

data have;
  input category $ type $ percent order;
datalines;
X Full 1 1
X B -.04 .
X A -.02 .
X E 0 .
X D .03 .
X C .01 .
X F .6 .
X sum1 . .
X sum2 . .
;
run;


proc sql noprint;
  select sum(percent)
  into :sum
  from have
  where type in ('A','B','C','D','E');
  select percent
  into :F
  from have
  where type='F';
  select percent
  into :maxperc
  from have
  where type="Full";
quit;


data have_mod;
  set have;
  if type='sum1' then percent=1+&sum.;
  if type='sum2' then percent=1+&sum.-&F.;
run;


proc sort data=have_mod(where=(percent<0)) 
  out=neg;
  by percent;
run;


data neg;
  set neg;
  order=_N_+1;
run;


proc sql noprint;
  select max(order) into :max_neg from neg;
quit;


proc sort data=have_mod(where=(percent>0 and type not in ("Full","sum1","sum2","F"))) 
  out=pos;
  by descending percent;
run;


data pos;
  set pos;
  order=_N_+&max_neg.;
run;


proc sql noprint;
  select max(order) into :max_pos from pos;
quit;


data x_have;
  set have_mod(where=(type in ("Full", "F", "sum1", "sum2"))) neg pos;
  if type='sum1' then order=1+&max_pos.;
  if type='F' then order=2+&max_pos.;
  if type='sum2' then order=3+&max_pos.;
run;


proc sort data=x_have;
  by order;
run;


proc sql noprint;
  select percent, order
  into :sum1, :ordersum1
  from x_have
  where type='sum1';
quit;


data want;
  set x_have;
  by category;
  if first.category then sumpercent=0;
  sumpercent+percent;
  if type in ('sum1','F','sum2') then sumpercent=percent;
  if type in ('Full','sum1','sum2') then high=sumpercent;
  else if type='F' then high=&sum1.;
  else if order=&ordersum1.-1 then high=&sum1.;
  else if order=2 then high=&maxperc.;
  else if percent>0 then high=sumpercent;
  else if percent<0 then high=sumpercent+abs(percent);
  if type in ('Full','sum1','sum2') then low=0;
  else if percent<0 then low=sumpercent;
  else if type='F' then low=high-sumpercent;
  else if percent>0 then low=sumpercent-percent;
run;


proc sgplot data=want;
  highlow x=type high=high low=low / group=type type=bar 
  groupdisplay=cluster highlabel=percent lineattrs=graphoutlines
  dataskin=matte;
  xaxis display=(nolabel noticks);
  yaxis offsetmin=0;
run;

SAS "Double" Waterfall sgplot

1 Answers