Using ODS to make your tables look pretty

Introduction

The introduction of ODS into the SAS programming language at version 7 onwards was one of the most major and far-reaching changes the software ever went through. The SAS language, up until that point, had acquired the reputation as being a "back-office system" and by that is meant that the "management" in an organisation are kept away from its rather dull plain-text reports, generated and kept in a back office somewhere, and the results from those reports get rewritten into a more attractive form, such as colorful tables, that then get presented to management. It was not good for the SAS Institute to have their software relegated to the background in this way and not good for the future of the organisation, so the decision was made to redesign the software so that the values coming out of sas procedures could be more easily accessed and the presentation of the results improved to match those of less expensive existing office software.

As a programmer, I arrived very late onto the scene of using ODS.  My initial use for it, as a clinical programmer, was picking up the statistical values coming out of stats procedures. Only in 2007 did I turn my attentions into using ODS to improve the layout of what were previously purely text tables. I expected to have to learn a lot, to get out what I wanted but was pleasantly surprised to discover that I did not need to learn anything at all. ODS does all the work for you.

I am assuming that your tables are coming out of "proc report" or perhaps "proc tabulate" (that I haven't used in years) because it is only sas procedures that have ODS support. If you produce reports using "data _null_" then ODS facilities exist but it is no easy matter.

The Big Secret

The big secret about using ODS to make attractive tables is................................................. there IS no big secret.

All you have to do is declare an RTF ("Rich Text Format") file name and optionally a template style before you call a sas procedures and then close the RTF stream after the procedure ends. Like this:
 
ods rtf file="file-destination" style=name-of-style;

proc report ...... ;
...;
run;

ods rtf close;

What you end up with is your normal plain-text output but with an extra output in "Rich Text Format" (RTF) in the file name you specified that can be incorporated into a word processor document. There you will be able to manipulate the table, edit it, color it, change its text, its font, its size etc..

Many other output formats are available. For example, "ods html" will give you html output.

As I mentioned previously, I am very late onto the scene of ODS for table output and I assumed it would have only limited capabilities. I was wrong. I am going to show you an example that I thought ODS would not be able to cope with but it coped with it perfectly.

An example

This is a complicated example of a table being converted to RTF format by ODS. If ODS can cope with this then I expect it to cope with anything. The complicated thing about this table is that some of the important rows, the variable label lines, are put there by a "line" statement in "proc report" and they are given a start column. I thought that that would make these lines entirely independent from ODS handling and outside the cell structure of tables but it turns out not to be the case. Here is the example code that will need some explaining. It is not the complete code and is copied and amended from the %unistats PowerPoint demonstration that can be linked to from the main page.
 
footnote1 j=l "^ Fisher's exact test" "%sysfunc(repeat(%str( ),200))" ;
footnote2 j=l "# Student's t test" "%sysfunc(repeat(%str( ),200))" ;

%unistats(dsin=demog,unicatrep=yes,total=yes, 
trtlabel="Number of Patients (%) /" "Descriptive Statistics" " ", 
lowcasevarlist=sexcd racecd, 
stats=N Min Mean Max STD.,minfmt=3.,maxfmt=3., 
varlist=sexcd racecd agecat age weight/m, 
pvarlist=sexcd racecd agecat age weight,
topline=no,pctsign=yes,
allcatvars=racecd agecat,
odsrtf=file="C:\spectre\tab1.rtf" bodytitle,
odshtml=file="C:\spectre\tab1.html"
);

What you see above is a call to my %unistats macro. It will produce a plain text table, an RTF table and an HTML table. You can see near the end of the call to the macro the two parameters odsrtf= and odshtml= and this gets used inside the macro just before the "proc report" step. It has to be done this way, rather than set up the "ods rtf" and "ods html" statements before the macro call, as the macro calls stats procedures that would be affected by the "ods" statements and I do not want to capture their output. I just want the table so that is why these statements must be enacted inside the macro just before the "proc report" step. Notice that the odsrtf= parameter value contains the word "bodytitle" at the end (works with SAS v9.1.3). This is to force titles and footnotes to be shown with the table rather than in document header and trailer lines. The odshtml= call does not need this. Now, if you look at the start of the code, I will explain the footnote statements. I have the option "center" in effect so I left-align the footnotes for the plain text report by padding out with spaces. This is what the %sysfunc() call is doing. However, this technique of padding out with spaces will get ignored in the RTF and HTML output so I have to use the "j=l" (justification equals left) statement. For the plain text report, the "j=l" will be ignored but it will be used for the RTF and HTML output. If I didn't want the plain text output then I could suppress it with the statement "ods listing close;" before the call to the macro and reinstate listing output using "ods listing;" afterwards but the plain text output is useful for a comparison with the rtf table and html table.

When I run the code, these are the outputs I get:

Plain text table
 
                                                    Number of Patients (%) /
                                                     Descriptive Statistics

                                            Ambicor        Betamaxin
                                            (1g/day)      (500mg/day)        Total
                                             (N=9)           (N=8)           (N=17)       p-value
__________________________________________________________________________________________________

Gender
    Male                                    7 ( 77.8%)      1 ( 12.5%)      8 ( 47.1%)     0.015^
    Female                                  2 ( 22.2%)      7 ( 87.5%)      9 ( 52.9%)

Race
    Caucasian                               5 ( 55.6%)      1 ( 12.5%)      6 ( 35.3%)     0.239^
    Black                                   2 ( 22.2%)      3 ( 37.5%)      5 ( 29.4%)
    Asian                                   2 ( 22.2%)      3 ( 37.5%)      5 ( 29.4%)
    Hispanic                                0 (  0.0%)      0 (  0.0%)      0 (  0.0%)
    Other                                   0 (  0.0%)      1 ( 12.5%)      1 (  5.9%)

Age (yrs)
    <16 yrs                                 0 (  0.0%)      0 (  0.0%)      0 (  0.0%)    >0.999^
     16 - 25 yrs                            4 ( 44.4%)      4 ( 50.0%)      8 ( 47.1%)
     26 - 40 yrs                            5 ( 55.6%)      4 ( 50.0%)      9 ( 52.9%)
     41 - 65 yrs                            0 (  0.0%)      0 (  0.0%)      0 (  0.0%)
    >65 years                               0 (  0.0%)      0 (  0.0%)      0 (  0.0%)

    N                                       9               8              17 
    Min                                    16              16              16 
    Mean                                   26.3            24.0            25.2            0.569#
    Max                                    40              36              40 
    STD.                                    8.03            8.49            8.07 

Weight (kg)
    N                                       9               8              17 
    Min                                    65.5            65.5            65.5 
    Mean                                   76.6            70.8            73.8            0.031#
    Max                                    79.6            78.1            79.6 
    STD.                                    4.36            5.69            5.71 
 
 

^ Fisher's exact test
# Student's t test

html table

The html output looks good but note that the column headers for "Total" and "p-value" do not go down to the bottom line as they should do. You can link to the html table here.

rtf table

What you will see is the default layout for the rtf table. You can specify a style if you want to to improve the look but you can edit the table to change colors and fonts from within a word processor. The rtf table is best viewed using a word processor such as MS Word so that you can see how easy it is to edit the table and improve its layout. You can link to the rtf table here.
 
 
 
 


 
 

Go back to the home page.

E-mail the macro and web site author.