turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Create dataset for waterfall chart?

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-28-2017 02:24 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to afiqcjohari

03-28-2017 03:00 AM

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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

All Replies

Solution

03-28-2017
03:35 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to afiqcjohari

03-28-2017 03:00 AM

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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

03-28-2017 03:35 AM - last edited on 03-28-2017 08:15 AM by ChrisHemedinger

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

03-28-2017 11:14 AM

And for lazy programmers:

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