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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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