BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
afiqcjohari
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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?

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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?

afiqcjohari
Quartz | Level 8

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

ballardw
Super User

And for lazy programmers:

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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