topic Re: Create dataset for waterfall chart? in SAS Programming

Create dataset for waterfall chart?

afiqcjohari — Tue, 28 Mar 2017 06:24:15 GMT

I often come across tasks where I need to present the drop off total counts as certain criteria are applied to the original base population.

And the table is then presented in this manner

input name $ count type $;
datalines;
Total Base 1000000 Base
first filter 900000 Filter
second filter 500000 Filter
third filter 400000 Filter;

The table shows the total number after each filter has been applied consecutively.

However, the waterfall chart http://blogs.sas.com/content/iml/2015/04/27/cascade-chart.html requires a slightly different

dataset.

datalines;
Total Base 1000000 Base
first filter -100000 Filter
second filter -400000 Filter
third filter -200000 Filter;

Note that the number of filter may varies from time to time. How can I generate the second table before applying the proc sgplot?

Re: Create dataset for waterfall chart?

Kurt_Bremser — Tue, 28 Mar 2017 07:00:16 GMT

Try this:

data have;
infile datalines dlm=',';
input name :$20. count type $;
datalines;
Total Base,1000000,Base
first filter,900000,Filter
second filter,500000,Filter
third filter,400000,Filter
;
run;

data want;
set have (rename=(count=oldcount));
retain keepcount;
if type = 'Base'
then do;
  keepcount = oldcount;
  count = oldcount;
end;
if type = 'Filter'
then do;
  count = oldcount - keepcount;
  keepcount = keepcount + count;
end;
drop keepcount oldcount;
run;

proc print data=want noobs;
run;

Note that this produces a different value for the third filter:

    name          type       count

Total Base       Base       1000000
first filter     Filter     -100000
second filter    Filter     -400000
third filter     Filter     -100000

Did I miss something, or was that a miscalculation in your example?

Re: Create dataset for waterfall chart?

afiqcjohari — Tue, 28 Mar 2017 12:15:13 GMT

Terrific! Yes, you are right, the last value was a miscalculation. Apologize. Thanks @Kurt_Bremser!

Re: Create dataset for waterfall chart?

ballardw — Tue, 28 Mar 2017 15:14:38 GMT

And for lazy programmers:

data want;
   set have;
   dif=dif(count);
   if type ne 'Base' then count=dif;
   drop dif;
run;