DATA Step, Macro, Functions and more

Create dataset for waterfall chart?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

Create dataset for waterfall chart?

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?


Accepted Solutions
Solution
‎03-28-2017 03:35 AM
Super User
Posts: 7,832

Re: Create dataset for waterfall chart?

Posted in reply to afiqcjohari

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎03-28-2017 03:35 AM
Super User
Posts: 7,832

Re: Create dataset for waterfall chart?

Posted in reply to afiqcjohari

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 99

Re: Create dataset for waterfall chart?

[ Edited ]
Posted in reply to KurtBremser

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

Super User
Posts: 11,343

Re: Create dataset for waterfall chart?

Posted in reply to KurtBremser

And for lazy programmers:

data want;
   set have;
   dif=dif(count);
   if type ne 'Base' then count=dif;
   drop dif;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 152 views
  • 1 like
  • 3 in conversation