How to create nice tables using PROC REPORT and ODS RTF output

Question

I want to create a 'nice looking table' using the SAS ODS RTF output and the PROC REPORT procedure. After spending the whole day on Google I've managed to produce the following:

The dataset

DATA survey;
   INPUT id var1 var2 var3 var4 var5 var6 ;
   DATALINES;
 1  1  35 17  7 2 2
17  1  50 14  5 5 3
33  1  45  6  7 2 7
49  1  24 14  7 5 7
65  2  52  9  4 7 7
81  2  44 11  7 7 7
2   2  34 17  6 5 3
18  2  40 14  7 5 2
34  2  47  6  6 5 6
50  2  35 17  5 7 5
;
RUN;

DATA survey;
    SET survey;
    LABEL var1 ='Variable 1';
    LABEL var2 ='Fancy variable 2';
    LABEL var3 ='Another variable no 3';
RUN;

LIBNAME mylib 'C:\my_libs';
RUN;

PROC FORMAT LIBRARY = mylib.survey;
    VALUE groups 1 = 'Group A'
                2 = 'Group B'
    ;

OPTIONS FMTSEARCH = (mylib.survey);

DATA survey;
    SET survey;
    FORMAT var1 groups.;
RUN; 

** The code for creating the rtf-file **

ods listing close;
ods escapechar = '^';
ods noproctitle;

options nodate number;
footnote;

ODS RTF FILE = 'C:\my_workdir\output.rtf' 
author = 'NN'
title = 'Table 1 name'
bodytitle 
startpage = no
style = journal;
options papersize = A4 
orientation = landscape;

title1 /*bold*/ /*italic*/ font = 'Times New Roman'  height = 12pt justify = center underlin = 0 color = black bcolor = white 'Table 1 name';
footnote1 /*bold*/ /*italic*/ font = 'Times New Roman'  height = 9pt justify = center underlin = 0 color = black bcolor = white 'Note: Created on January 2012';

PROC REPORT DATA = survey nowindows headline headskip MISSING
    style(header) = {/*font_weight = bold*/ font_face = 'Times New Roman' font_size = 12pt just = left}
    style(column) = {font_face = 'Times New Roman' font_size = 12pt just = left /*asis = on*/};
    COLUMN var1 var1=var1_n var1=var1_pctn;
    DEFINE var1 / GROUP ORDER=FREQ DESCENDING 'Variable';
    DEFINE var1_n / ANALYSIS N 'Data/(N=)';
    DEFINE var1_pctn / ANALYSIS PCTN format = percent8. '';
RUN;

ODS RTF CLOSE;

This generates an RTF table in Word something like the following (a little simplified):

However, I want to add a variable lable 'Variable 1, n (%)' above the groups in the variable name column as a separate row (NOT in the header row). I also want to add additional variables and statistics in an aggregated table.

In the end, I want something that looks like this:

I have tried "everything" - is there anyone who knows how to do this?

Answer 1

I know this has been open for awhile, but I too was struggling with this for awhile, and this is what I figured out. So...

In short, SAS has trouble outputting nicely formatted tables that contain more than one type of table "format" in them. For instance, a table where the columns change midway through (like you commonly find in the "Table 1" of a research study describing the study population).

In this case, you're trying to use PROC REPORT, but I don't think it's going to work here. What you want to do is stack two different reports on top of each other, really. You're changing the column value midway through and SAS doesn't natively support that.

Some alternative approaches are:

• Perform all your calculations and carefully output them to a data set in SAS, in the positions you want. Then, use PROC PRINT to print them. This is what I can only describe as a tremendous effort.

• Create a new TAGSET that allows you to output multiple files, but removes the spacing between each one and aligns them to the same width, effectively creating a single table. This is also quite time consuming; I attempted it using HTML with a custom CSS file and tagset, and it wasn't terribly easy.

• Use a different procedure (in this case, PROC TABULATE) and then manually delete the spacing between each table and fiddle with the width to get a final table. This isn't fully automated, but it's probably the quickest option.

PROC TABULATE is cool because you can use multiple table statements in a single example. Below, I put some code in that shows what I'm talking about.

DATA survey;
   INPUT id grp var1 var2 var3 var4 var5;
   DATALINES;
     1  1  35 17  7 2 2
    17  1  50 14  5 5 3
    33  1  45  6  7 2 7
    49  1  24 14  7 5 7
    65  2  52  9  4 7 7
    81  2  44 11  7 7 7
    2   2  34 17  6 5 3
    18  2  40 14  7 5 2
    34  2  47  6  6 5 6
    50  2  35 17  5 7 5
;
RUN;

I found your example code to be a little confusing; var1 looked like a grouping variable, and var2 looked like the first actual analysis variable, so I slightly changed the code. Next, I quickly created the same format you were using before.

PROC FORMAT;
    VALUE groupft 1 = 'Group A' 2 = 'Group B';
RUN;

DATA survey;
    SET survey;
    LABEL var1 ='Variable 1';
    LABEL var2 ='Fancy variable 2';
    LABEL var3 ='Another variable no 3';
    FORMAT var1 groupft.;
RUN;

Now, the meat of the PROC TABULATE statement.

PROC TABULATE DATA=survey;
    CLASS grp;
    VAR var1--var5;
    TABLE MEDIAN QRANGE,var1;
    TABLE grp,var2*(N PCTN);
RUN;

TABULATE basically works with commas and asterisks to separate things. The default for something like grp*var1 is an output where the column is the first variable and then there are subcolumns for each subgroup. To add rows, you use a column; to specify which statistics you want, you add a keyword.

This above code gets you something close to what you had in your first example (not ODS formatted, but I figure you can add that back in); it's just in two different tables.

I found the following papers useful when I was tackling this problem:

http://www.lexjansen.com/pharmasug/2005/applicationsdevelopment/ad16.pdf

http://www2.sas.com/proceedings/sugi31/089-31.pdf

Answer 2

1 ODS has some interesting formatting features (like aligning the numbers so a decimal point goes at the same column) but their usefulness is limited for more complex cases. The most flexible solution is to create a formatted string yourself and bypass PROC REPORT's formatting facility completely, like:

data out;
    length str $25;
    set statistics;
    varnum = 1;
    group = 1;
    str = put( median, 3. );
    output;
    group = 2;
    str = put( q1, 3. ) || " - " || put( q3, 3. );
    output;
run;

You can set varnum and group as ORDER variables in PROC REPORT and add headings like "Variable 1" or "Fancy variable 2" via COMPUTE BEFORE; LINE

2 To further keep PROC REPORT from messing up the layout in ODS RTF output, consider re-enabling ASIS style option:

define str / "..." style( column ) = { asis= on };