Waterfall Plots

Clinical data reporting often uses the waterfall plot to display how subjects are changing related to each other or how much they are changing.  A waterfall plot is basically a bar graph where the bars are ordered by decreasing height; thus, it is intended to look somewhat like a waterfall gradually falling to the right.

I have written a macro that will create a waterfall plot for the user with just a few input variables (and of course, some additional options if so desired).

Before I get started, I must say that I am not a huge fan of SAS waterfall plots.  If you do not care why I use SAS over R, then please skip to The Macro Description section.  I have heard that with the new SAS 9.3 that there are some SG procedures that can produce these plots quite easily and attractively.  I do not have access to SAS 9.3 (thanks to the kind programmers at SAS Institute who require paychecks).  I still use SAS 9.1.3.  I have always been more fond of my waterfall plots in R (which you can compare here).

The only reason I have settled with SAS plots is because it became too much of a management issue for me.  First, let me explain my SAS to R process (There is a SAS to R to SAS method that I have yet to figure out).  Since SAS is great for data management and not so wonderful when it comes to graphics while R is fabulous with graphics while not so user friendly when it comes to data manipulation (this may just be a personal opinion), I would manipulate my data in SAS, export it to a .csv Excel file, read it into R, perform minor manipulations if needed, and output a PDF of the graph.

Now my issues...

First, my plots are usually not wanted in PDF because it makes it difficult to copy into PowerPoint presentations for management.  Second, my overseers have this loving habit of asking me to produce a plot.  They tell me how wonderful it looks, but they would like to see what happens if I change just one criteria.  Well, they are not programmers, so they do not realize that sometimes that one change requires quite a bit more programming--not necessarily getting the result, but getting the output to adjust.

In short, to skip all that hassle, I have just dropped my R waterfall plots (sorry my wonderful  R graphics, maybe we can get together some other time) and moved on to SAS.  R is kind of like that girl that you really wanted to date in high school, but she was way too classy for you.  So, you went out with the next best thing because she like you back.

The Macro Description

Okay, back to the macro!  This macro is called %waterfall (yes, a very creative name--this is why programmers get paid the big bucks).   %waterfall  requires 4 input parameters with 5 more to enhance presentation.  There is some internal stuff that can be changed around if you want to even more personalize the output for your liking.  Take time to adjust the code in this macro to make it fit your company profile or your personal programming preferences!

Parameter	Required?	Default Value	Description
dsin	Yes		The input dataset used containing the data with at least yvar, byvar, and any variables required for the whr filtering
whr	No	1=1 (captures all observations in the dsin dataset)	Filters out any unwanted observations in the dsin dataset
yvar	Yes		The name of the variable containing the values that will be plotted on the y-axis
byvar	No		A grouping variable if you want multiple waterfalls side by side (e.g., suppose you want to compare males to females)
title	No		Plot title
ylab	No		Label for the y-axis
outpath	Yes		The path for the output file
filename	Yes		The name of the output file
barwidth	No	3	The width of the waterfall bars

The Macro Code

This is a very straightforward macro for those familiar with SAS programming.  I have tried to leave sufficient comments throughout the code, but feel free to contact me with questions (via e-mail or by leaving a comment).

%macro waterfall(dsin=, whr=%str(), yvar=, byvar=, title=%str(), ylab=%str(), outfile=%str(), filename=%str(), barwidth=3);

      /* Display error message if WATERFALL dataset is attempting to be overwritten */

      %if %sysfunc(upcase(&dsin.))=WATERFALL %then %do;

            %put ERROR: The dataset WATERFALL is used in the waterfall macro.  Please rename your input dataset and rerun the macro;

            %return;

      %end;

      /* Get variable type and format of BYVAR */

      proc sql noprint;

            select type, format into :vartyp, :varfmt from sashelp.vcolumn

            where upcase(memname)=upcase("&dsin.") & upcase(name)=upcase("&byvar.");

      quit;

      /* Subset DSIN into WATERFALL & sort */

      data waterfall;

            set &dsin.;

            where &whr.;

      run;

      proc sort data=waterfall;

            by &byvar. descending &yvar.;

      run;

      data waterfall;

            set waterfall;

            n=_n_;

      run;

      /* Set up BYVAR distinct values and number of distinct values */

      proc sql ;

            select count(distinct &byvar.) into :nby from waterfall;

            %let nby=%sysfunc(compress(&nby.));

            %if &nby.=1 %then %do;

                  select distinct &byvar. into :by1 from waterfall;

            %end;

            %if &nby.>1 %then %do;

                  select distinct &byvar. into :by1-:by&nby. from waterfall;

            %end;

      quit;

      /* Create waterfall plot */

      %let col1=blue;         %let col2=red;    %let col3=yellow; %let col4=green;  %let col5=purple;

      %let col6=orange; %let col7=pink;   %let col8=brown;  %let col9=gray;         %let col10=black;

      options formchar="|____|+|___+=|_/\<>*" pageno=1 nonumber nodate orientation=landscape center;

      ods listing close;

      ods rtf file="&outfile.\&filename..rtf" style=journal;

      goptions reset=all ftext=swissl ftitle=swissl colors=(black) hsize=9 in vsize=6.5 in;

      axis1 label=(angle=90  "&ylab.") order=(-100 to 150 by 50) minor=(n=4);

      axis2 label=(' ') value=none major=none  minor=none;

      legend1 across=1 mode=protect position=(top left inside) cborder=black label=none value=(j=c %do i=1 %to &nby.; "&&by&i." %end;);

      title1 h=2 "&title.";

      proc gplot data=waterfall;

            plot &yvar.*n=&byvar. / vaxis=axis1 haxis=axis2 legend=legend1;

            where &yvar.^=.;

            %do i=1 %to &nby.;

                  symbol&i. interpol=Needle value=none width=&barwidth. color=&&col&i.;

            %end;

      run;

      quit;

      ods rtf close;

      ods listing;

      title; footnote;

      proc datasets lib=work;

            delete waterfall;

      run;

      quit;

%mend waterfall;

Example

First, please note from the first few lines of the macro that you cannot name your input dataset WATERFALL.  I guess you could, but you will get an error message, and your macro will not run.  This is set up so that you do not accidentally overwrite any desired data.

With that said, let's create a dataset with a more creative name like WATERFALLDATA.

data waterfalldata;

      input patient $ change gender $ @@;

      cards;

001   64    Male        002   0     Female

003   -30   Female      004   -42   Female

005   7     Female      006   19    Male

007   4     Female      008   0     Male

009   0     Male        010   -100  Female

011   -19   Female      012   -3    Female

013   14    Female      014   28    Male

015   -13   Male        016   -67   Female

017   -50   Female      018   59    Female

019   27    Female      020   -24   Male

021   16    Female      022   -54   Female

023   35    Male        024   -69   Male

025   -9    Female      026   61    Female

027   -19   Female      028   95    Male

029   3     Female      030   -5    Male

031   107   Male        032   -2    Female

033   65    Male        034   78    Female

035   65    Female      036   -41   Female

037   12    Female      038   15    Male

039   -13   Male        040   -35   Female

041   -21   Male        042   15    Female

043   35    Female      044   -54   Male

045   21    Female      046   10    Male

047   -100  Male        048   10    Female

;

run;

If you have followed any of my other posts, you will know that I love to write reusable macros, save them in a separate file, and call them using the %inc statement.  It makes the current program less crowded

%inc 'D:\Documents and Settings\dbateman\Desktop\Reusable Macros\waterfall.sas';

%waterfall(dsin=waterfalldata, yvar=change, byvar=gender,

            title=%str(Best Change in Sum of Longest Diameter by Gender), ylab=%str(Best Change in Sum LD),

            outpath=%str(D:\Documents and Settings\dbateman\Desktop\Waterfall), filename=%str(waterfall_example), barwidth=8);

And with that short amount of code, you can create this beautiful thing:

Abbreviations and Keyboard Macros

There are times when you will use SAS code over and over and over again.  The whole copy and paste trick is fine, but sometimes you have to open up the file that you want to copy from.  There even arises the issue of trying to find the file that you want to copy from.  Using abbreviations and keyboard macros can keep your often-used code right at your fingertips (literally).



Creating Abbreviations

To create an abbreviation, start a SAS editor and from the menu bar select Tools -> Add Abbreviation...

This will display a pop-up box. Type the abbreviation name in the “Abbreviation:” box and paste the SAS code in the “Text to insert for abbreviation:” box.  In the example that I have here, I called my abbreviation "example."  The text that is associated with this abbreviation is: 

Type this line of text every time I type the word "example" in a SAS editor.  That way, I will not have to type it over and over and over again.

Now, whenever I type the word "example" in a SAS editor, SAS will turn the word red and display a small box that shows the first several characters of the saved abbreviation.

By pressing the tab key, the saved text will automatically appear.

Notice that my text is shown exactly as it is in the "Add Abbreviation" box.  I could have put all the text on one line.  I broke the lines for visual aid here.  SAS does not have a line length limit or a line count limit, so you can have your text be as long as you want, both vertically or horizontally.

If you want to use the actual word "example" in your SAS code without engaging the saved text, simply keep typing.  If you do not press tab, it will never display the saved text.

Editing Abbreviations

What if you realize that you misspelled a word or you just want to change what your text says?

From the SAS editor menu bar select Tools -> Keyboard Macros -> Macros...
(Once the abbreviation has been created, it is referred to as a "keyboard macro.")

This will display another pop-up box that lists all the keyboard macros that you have created.  Simply select the abbreviation name that you want to edit, and click on the "Edit..." button.  This will display yet another pop-up box.  Click "Modify" to get the box where you may edit anything you need.



Real-life Examples

I have created several of these keyboard macros, mostly for the purpose of recalling the parameters required for macros that I have written as well as their location to include in the SAS program.  But two that I use in every program are header comments and output code.

Header Comments

Header comments are the information that should appear at the top of every program.  It should give some detail like the name and date of the program, the name of the programmer, the production path of the program, and the purpose of the program.  It is also useful to have a history log to show who, when, and what was changed if anyone ever makes changes to the program.

I have already saved the macro text, so I will just show you how I get it and what the output looks like.

First, I named my keyboard macro "header."  So, by typing the word "header" in a SAS editor, it recognizes that I have a keyboard macro with that name and prompts me with a reminder that I can use the saved macro text:

Next, after I press the tab key, the following saved text is automatically displayed.

SIMPLE!  Now I just have to change the "XXX" placeholders, change any information that may need adjusted, and fill in any blank spaces.


Output Code

Your soul purpose as a SAS programmer is to provide output for your boss or for your clients.  I have posted a whole discussion on how to make standardized output.  Since I generate so much ODS output, I am using the same code over and over again.  This saves me a lot of time.
Again, I have already saved the macro text previously.  I named this keyboard macro "outready." So, by typing the word "outready" in the SAS editor, it recognizes that I have a keyboard macro with that name and prompts me with a reminder that I can use the saved macro text:

After I press the tab key, the following saved text is automatically displayed.

Once again, it's as simple as that!  All that is left is to change any place holders or adjust any other information.

I got all that text for a header and for output just by typing two small words.



But what if I forget what my keyboard macro is called or if I even have one saved?

This can be resolved in one simple step:

1.  Bring up the keyboard macros and search the list of names that are displayed:

When you create keyboard macros you can add a description to remind you of what the key word will do. It is also possible to assign quick keys to your keyboard macros. As you can see, I have not taken the time to do that. But be sure not to overwrite any other quick keys that you usually use.


For example, I could assign the keyboard macro "header" as ctrl+H, but that would overwrite the find-and-replace function that I use on a daily basis.  It is find to overwrite such functions as long as you are sure that you will never use them.

Have fun creating all of your own keyboard macros!



Standardized Output

If you are programming clinical tables, listings, and graphs (TLG's), then there is a high probability that you are either working for a company or consulting for one.  I have yet to meet someone who programs these things for fun.

I take that back.  Maybe my friend Karl from grad school would do this kind of thing.

When creating output, SAS can be quite helpful in changing things as needed, but the original default output is pretty ugly to look at.  Here is what I have put together to create a standardized output.


Output Features


My output consists of 4 main sections: headers, footers, titles, and footnotes.  Some may think that a header is the same as a title and that a footer is the same as a footnote.  This is because when a TITLE statement or FOOTNOTE statement is defined in SAS, they are placed in the header and footer sections of a Word document, respectively.  Read on to find how you can split them out.

The following features are pretty basic across all TLG's:

• Headers
• Company Name - Most companies or clients like to have their name attached to each TLG.  It seems to give greater authority and ownership to the output.
• Study Identifier - A TLG is usually the result of a specific study run by the company/client.  This is a good place to display that protocol identifier, drug name, or any other way of identifying the study. 
• Page Counter  - Not all TLGs fit on one page.  This is especially true when it comes to listings, even for  Phase I studies.  Knowing how many pages are in the output and which page is which keeps things organized.
• Footers
• Program Identifier  - It is not rare that your manager will be digging through a pile of papers, find a table that shows an interesting analysis, and want that reproduced for a new study.  To be sure that it is reproduced using the same methodology, it is nice to be able to refer to the original program.  The program identifier shows the programmer either the location or the name of the program used.
• Production Date  - Things change.  Data gets cleaned.  New data comes in.  Suppose your output shows that your drug increases survival by 8 weeks.  You get new data and find that the rate has changed to 7 weeks, but your boss still has the output that says 8 weeks.  Having the date stamped on the table will allow you to defend why you report 7 weeks while your manager claims 8 weeks.  You simply need to explain that his/her output is three months old and that the data has since been updated.
• Titles
• Main Title - This is a quick description of the output.
• Subtitle - I like to use this to specify the population being used, but it can be used (or omitted) for you specific purposes.
• Footnotes
• Superscripted Footnote Text  - Many people like to make the body of the table look cleaner by placing minimal information.  But the information may not make sense without explanations.  Place a superscripted number in the output and reference it to the footnote where more detailed information is given.
• Notes  - Any additional notes that are not specific to a place in the output can be detailed here.  Perhaps the methods used, the type of regression used, the type of p-value reported, etc.
• Keys - You may know that CI means confidence interval, but not everyone does.  This is a good place to lay out the meaning of any abbreviations that you use in your output.

SAS has many output styles.  Here are several examples.  I like the look of the Journal style the best, so this is what I will use throughout these posts (until I find something better).  It seems more professional than any other SAS style that I have come across.  Keep in mind that if you really want to customize your own style, you may use PROC TEMPLATE to create your own style.  I have not taken the time to get to know PROC TEMPLATE, but I am sure it can come in handy if your company/client demands a specific look.

Let me now show the code that I use when creating standardized output loaded with comments.  Following all the commented code will be a cleaner repeat in an example.



/*****************************************************************************************************************/ 

/***  Specify some options.  These are mostly for HTML output, but some are used for RTF or PDF.  I like to have my tables landscape and centered, but you are welcome to change the orientation to portrait and adjust the justification of the overall table as you wish.  ***/
options formchar="|____|+|___+=|_/\<>*" pageno=1 nonumber nodate orientation=landscape center;

/***  Close ODS listing so that output will go to your preferred output rather than to the SAS output screen.  Specify which ODS you want to use (I have RTF specified here).  Assign an output file path and name.  &outfile. is a macro that I specify at the top of my programs so that all output goes to the same place.  XXX is the name of the file.  Again, I like the journal style, so I specify style=journal here.  The ODS ESCAPECHAR='~'; statement will come into play later.  ***/

ods listing close;

ods rtf file="&outfile.\XXX.rtf" style=journal;

ods escapechar='~';

/***

SAS has several automatic variables.  One that I like o use is SYSUSERID.  This is the user ID of the person logged into the computer that produces the output.  I use this to attach to the program identifier so people know that it was me who ran the program.  I format this and store it in a macro variable (USER) for later.  If you do not want the user ID formatted, then simply ignore this line and replace &user. with &sysuserid. in the FOOTNOTE1 statement.

The production date can simply by saved into a macro variable (PRODDT) by caling the date() function within the %sysfunc() macro function and assigning it the date format of your choice.

***/

%let user=%sysfunc(upcase(&sysuserid.));

%let proddt=%sysfunc(date(),worddate.);

/************************************/

/***  DEFINE HEADERS AND FOOTERS  ***/

/************************************/

/***

The first line will have the company name and the page counter.  This is where the ODS ESCAPECHAR='~'; statement from above comes into play.  The tilde (~) is used to specify that what follows in brackets ({}) is an inline formatting function.  You may use any character that you want, but it should be one that is most likely not going to appear anywhere else in your code except where you want to specify these inline formatting characters.  ~n specifies a new line.

Here, I use TITLE1 to put the company name in the upper left corner and the page in the upper right corner.  I use TITLE2 to place the protocol identifier directly below the company name.

Next, you can see in the FOOTNOTE1 statement where I specify to put the program name (and my user ID) in the bottom left corner and the production date in the bottom right corner.

***/

title1 j=left font='Arial' 'ABC Company, Inc.' j=right "Page ~{pageof}";

title2 j=left font='Arial' "Protocol: AZ1234";

footnote1 j=left font='Arial' "Program: OUTPUT.sas by &user." j=right "Date Produced: &proddt.";

 

/***

PROC REPORT is my choice procedure for outputting tables.  You may prefer PROC TABULATE or simply use the raw output from PROC MEANS, PROC FREQ, PROC UNIVARIATE, or any other PROC.  It doesn't matter.

data=XXX is the dataset that I want to output.

nowindows tells SAS not to open a new window for displaying output.

split='^' tells SAS that when a carrot (^) is found in a quoted string, then start a new line within that cell.  This comes in handy when putting group counts in the table headers.

style(column)={asis=on} will keep indentations as you specify them.  I like to have indented headers on occasion.  Without this, SAS will ignore leading spaces and make everything line up.

style(report)=... is my way of tweeking the style=journal parameters just a bit for my own liking.

pretext="..." is where I specify my main title and subtitle.  This will make the title part of the table rather than putting it in the document header.  If you use the TITLE statement, then the title will be placed up in the header.

style=[CellWidth=%] is what I use to make it easier to keep footnotes within the width of the table.  Otherwise, I may have a skinny table with very long footnotes that do not wrap within the table bounds.

For the rest of the syntax, please see "REPORT Procedure" in the SAS help files.

***/

proc report data=XXX nowindows split='^' style(column)={asis=on}

            style(report)={font=('Arial',9.5pt,bold italic) pretext="&maintitle. \line\ &subtitle."};

      column XXX YYY ZZZ;

      define XXX / display left ' ' style=[CellWidth=%];

      define YYY / display center ' ' style=[CellWidth=%];

      define ZZZ / display center ' ' style=[CellWidth=%];

run;

/***

This is where I specify footnotes.  You will need to replace the text, "SUM OF CellWidth FROM PROC REPORT DEFINE STYLES" with the sum of the CellWidth percents discussed above.  This will create a macro variable that will be used to keep the footnotes within the bounds of the table.


~(super 1) will create a supersripted 1.  You can replace the 1 with any other value or character of your choice.  Follow the ~(super 1) with any related text.


The second ods rft line will output any notes that you type here.


The third ods rtf line will output any abbreviations that you type here.

***/

%let margin=%sysfunc(cats(%eval((100-SUM OF CellWidth FROM PROC REPORT DEFINE STYLES)/2),%));

ods rtf text="~S={leftmargin=&margin. rightmargin=&margin. just=l font=('Arial', 9pt)}~{super 1} PLACE FOOTNOTE TEXT HERE";

ods rtf text="~S={leftmargin=&margin. rightmargin=&margin. just=l font=('Arial', 9pt)}NOTES:~nPLACE SPECIAL NOTES HERE";

ods rtf text="~S={leftmargin=&margin. rightmargin=&margin. just=l font=('Arial', 9pt)}~nKEY:  PLACE ANY ABBREVIATIONS HERE";

/***  Close the RTF ouput.  Reopen the listing so that output can be displayed in the SAS output window.  Clean up all titles and footnotes.  ***/

ods rtf close;

ods listing;

title; footnote;

/*****************************************************************************************************************/

 

Example

I am going to use the data and code from my last post to prep this example.  Please refer to that post if you want to see how the NUMSTATS macro works.

/* Call the summary macros */

%inc 'D:\Desktop\Reusable Macros\Summary Statistic Macros.sas';

%NUMSTATS(dsin=sashelp.class, var=age, dsout=age, srt1=1, byvar=sex, lbl=%str(Age~{super 1}));

%NUMSTATS(dsin=sashelp.class, var=height, dsout=height, srt1=2, byvar=sex, lbl=%str(Height~{super 2}));

%NUMSTATS(dsin=sashelp.class, var=weight, dsout=weight, srt1=3, byvar=sex, lbl=%str(Weight~{super 3}));

data demog;

    set age height weight;

run;

/* Insert blank lines if wanted */

proc sql;

    insert into demog (srt1)

        values (1.5)

        values (2.5);

quit;

/* sort the data so the inserted blank lines are in the correct place */

proc sort data=demog;

    by srt1 srt2;

run;

/* output the table */

options formchar="|____|+|___+=|_/\<>*" pageno=1 nonumber nodate orientation=landscape center;

ods listing close;

ods rtf file="D:\Desktop\Class Summary by Sex.rtf" style=journal;

ods escapechar='~';

/* Create macro variables for footer */

%let user=%sysfunc(upcase(&sysuserid.));

%let proddt=%sysfunc(date(),worddate.);

/* Create headers and footers */

title1 j=left font='Arial' 'ABC Company, Inc.' j=right "Page ~{pageof}";

title2 j=left font='Arial' "Study: AZ1234";

footnote1 j=left font='Arial' "Program: Output_Example.sas by &user." j=right "Date Produced: &proddt.";

/* Create the report with a title/subtitle */

proc report data=demog nowindows split='^' style(column)={asis=on}

        style(report)={font=('Arial',9.5pt,bold italic) pretext="Class Demographics \line\ (Grouped by Gender)"};

    column label m f;

    define label / display left ' ' style=[CellWidth=20%];

    define m / display center 'Male' style=[CellWidth=15%];

    define f / display center 'Female' style=[CellWidth=15%];

run;

/* Create a macro variable to keep the footnotes within the bounds of the table.

LABEL is 20%, M & F are each 15%.  The sum is 50, which is placed in the line below */

%let margin=%sysfunc(cats(%eval((100-50)/2),%));

/* Now we will create the footnotes: */

ods rtf text="~S={leftmargin=&margin. rightmargin=&margin. just=l font=('Arial', 9pt)}~{super 1} Age is defined in years.

                                                                                                                           ~n~{super 2} Height is defined in inches.

                                                                                                                             ~n~{super 3} Weight is defined in pounds.";

ods rtf text="~S={leftmargin=&margin. rightmargin=&margin. just=l font=('Arial', 9pt)}~nNOTES:~nThis is a very long footnote that is supposed to demonstrate how the footnote will wrap within the bounds of the table rather than with the bounds of the document when the margin macro variable is used on a very long footnote.";

ods rtf text="~S={leftmargin=&margin. rightmargin=&margin. just=l font=('Arial', 9pt)}~nKEY:~nSD = Standard Deviation~nMin = Minimum~nMax = Maximum";

ods rtf close;

ods listing;

title; footnote;

Here is the output: