How to create good tables using the PROC REPORT report and RTF ODS output

Question

I want to create a “pretty table” using the SAS ODS RTF output and the PROC REPORT procedure. After spending all day on Google, I managed to create the following:

Data set

 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; 

** 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 creates an RTF table in Word, something like the following (slightly simplified):

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

In the end, I want something similar to this:

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

Answer 1

I know that it was open for a while, but I also struggled with it for a while, and this is what I understood. So that...

In short, SAS has problems displaying well-formatted tables that contain more than one type of table "format" in them. For example, a table in which columns change halfway (as you usually find in the “Table 1” of the study describing the study population).

In this case, you are trying to use PROC REPORT, but I don't think it will work here. What you want to do is collect two different reports on top of each other. You change the column value halfway, and SAS does not support this.

Some alternative approaches:

• Perform all your calculations and carefully output them to the data set in SAS, in the positions you need. 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 them and aligns them to the same width, effectively creating a single table. It also takes a lot of time; I tried using HTML with a special CSS file and a set of tags, and it was not so simple.

• Use a different procedure (in this case PROC TABULATE), and then manually delete the interval between each table and the violin width to get the final table. It is not fully automated, but it is probably the fastest option.

PROC TABULATE is cool because you can use multiple table statements in one example. Below I put the code 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 sample code a bit confusing; var1 looked like a grouping variable, and var2 looked like the first actual analysis variable, so I changed the code a bit. Then I quickly created the same format you used 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 team.

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

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

This code gives you something close to what you had in the first example (not ODS formatted, but I suppose you can add it back); it's just in two different tables.

I found the following documents useful when I was solving 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 functions (for example, aligning numbers, so the decimal point goes in one column), but their usefulness is limited for more complex cases. The most flexible solution is to create a formatted string yourself and a complete workaround for formatting PROC REPORT, for example:

 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 headers like "Variable 1" or "Fancy variable 2" via COMPUTE BEFORE; LINE

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

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