topic Re: Formatting Proc Tabulate Output in SAS Procedures

Formatting Proc Tabulate Output

Doug — Mon, 07 May 2012 21:21:13 GMT

How do you produce output where parenthses appear in the output? For example, having a mean value followed by a standard deviation (in parentheses) in the same row, or a min/max?

Re: Formatting Proc Tabulate Output

FriedEgg — Mon, 07 May 2012 21:35:50 GMT

I will try to understand your question:

You have dataset: SASHELP.CLASS and you want to perform a PROC TABULATE on said data.

Such as,

PROC TABULATE DATA=SASHELP.CLASS;

VAR AGE;

CLASS SEX;

TABLE SEX,AGE*(MEAN,STDDEV);

RUN;

This produces output such as:

-----------------------------------------------------------

| | Age |

| |-------------------------|

| | Mean | StdDev |

|-------------------------------+------------+------------|

|Sex | | |

|-------------------------------| | |

|F | 13.22| 1.39|

|-------------------------------+------------+------------|

|M | 13.40| 1.65|

-----------------------------------------------------------

Instead of this format you want to see something like:

-----------------------------------------------------------

|Sex | Age |

|-------------------------------+-------------------------|

|F | 13.22(1.39) |

|-------------------------------+-------------------------|

|M | 13.40(1.65) |

-----------------------------------------------------------

Is this correct?

Re: Formatting Proc Tabulate Output

Doug — Mon, 07 May 2012 21:44:08 GMT

Yes that's correct - I would like parentheses to appear in the printed output around the s.d. statistic. I would also like to see how this is done in the REPORT procedure.

Re: Formatting Proc Tabulate Output

FriedEgg — Mon, 07 May 2012 21:52:55 GMT

*proc tabulate does not have a noprint option that I am aware of,

so you can choose to turn off ods and reopen it after the step to avoid printing it if necessary.

or use proc summary or others instead of tabulate;

proc tabulate data=sashelp.class out=foo(drop=_:);

var age;

class sex;

table sex,age*(mean stddev);

run;

data bar;

set foo;

statistics=cats(put(age_mean,4.2),'(',put(age_stddev,4.3),')');

keep sex statistics;

run;

proc print data=bar noobs; run;

Re: Formatting Proc Tabulate Output

Doug — Mon, 07 May 2012 21:56:15 GMT

What about PROC REPORT?

Re: Formatting Proc Tabulate Output

Reeza — Mon, 07 May 2012 23:03:14 GMT

None of the SAS procs will do that by default.

You'll need to get some customized reporting developed using either macros or some data manipulation.

There's a lot out there already around this, but you can look into a Macro from the Mayo Clinic that generates output in that format.

Re: Formatting Proc Tabulate Output

Cynthia_sas — Tue, 08 May 2012 00:28:26 GMT

Hi:

I have 3 examples of reports like this in my paper Creating Complex Reports. Two examples used PROC REPORT after some data manipulation and 1 example used DATA step and a custom TABLE template.

The paper link:

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

cynthia

Re: Formatting Proc Tabulate Output

Doug — Tue, 08 May 2012 13:05:30 GMT

Cynthia,

I've never done this type of reporting in SAS. Can you provide some code examples to me for further study? I am particularly interested in methods to produce Example 1 and Example 8 from your paper. -- Doug

Re: Formatting Proc Tabulate Output

Cynthia_sas — Tue, 08 May 2012 16:57:10 GMT

Hi:

If you go to http://support.sas.com/rnd/papers and search for the papers from 2008, you will see a download where you can find a PDF of the slide presentation and the programs used to create the paper. Do not search for my name, instead search on that page for "Creating Complex Reports" and you will find the link to download the zip file of programs. The programs for 1 and 8 are too long to post here.

cynthia

Re: Formatting Proc Tabulate Output

Doug — Thu, 10 May 2012 12:40:35 GMT

I have a file which is similar to yours and is laid out in the following format:

Total NN

Age

agex NN (%xx.x)

agey NN (%xx.x)

agez NN (%xx.x)

Sex

M NN (%xx.x)

F NN (%xx.x)

......

How do I set up proc report to print in journal style for this structure?

Re: Formatting Proc Tabulate Output

Cynthia_sas — Thu, 10 May 2012 12:52:40 GMT

Hi:

My paper showed how to use JOURNAL style. Using JOURNAL style is unrelated to PROC REPORT. JOURNAL style could be used with ANY procedure. The specification for style usage goes on your ODS invocation statement:

ODS <dest> FILE=file.ext STYLE=JOURNAL;

...your code;

ODS <dest> CLOSE;

cynthia

Re: Formatting Proc Tabulate Output

Doug — Thu, 10 May 2012 13:18:27 GMT

Ok - but REPORT is needed to get all the bold categories, underlining, column heads and so forth in the table, correct? If so, this is what I want to do. I apologize for asking so many questions...

Re: Formatting Proc Tabulate Output

Cynthia_sas — Thu, 10 May 2012 22:26:21 GMT

Hi:

The code in the paper and the explanation in the paper and on the slides is REALLY too long to post here. You could apply bolding to TABULATE output and you could apply underlining to TABULATE headers.

But the bottom line is that to do the same report and change the information in a single COLUMN, based on the value of a different COLUMN on the report will require either PROC REPORT and CALL DEFINE (Examples 1 and 1a in the paper) or a custom TABLE template (Example 8). In my opinion, TABULATE does not have quite enough flexibility such as you get with the COMPUTE block and/or CALL DEFINE.

Did you run the code in the paper from the ZIP file? Look at where the explanation for Example 1 starts on page 9 of the paper, summarized as Steps 1, 2 and 3 -- these 3 steps are all accomplished in the complex1_demog.sas program file. The patient data file is in the zip download. You need to make sure you have a LIBNAME that points to the location of the data. In my program, I made the LIBNAME make a libref called "COMPLEX" and the physical location for the data was in c:\complex directory. If you unzip the data to a different physical location, then you will need to change the LIBNAME statement accordingly. The code was originally written for SAS 9.1.3, so you will need at least that version of SAS to run the programs.

The steps are further explained on slides in the Complex_Reports_SGF.pdf file of slides in the download. For example, compare the original patient data on page 8 of the slides with the final data set shown on page 10 of the slides (the final dataset has these variables: BRKVAR GRPORD ROWORD VARINFO VALUE) -- my PROC REPORT step depends on this final (summarized) structure to the (summarized) data.BRKVAR, GRPORD and ROWORD are used as NOPRINT items in the PROC REPORT code. (Something that you cannot do with TABULATE.) VARINFO is the equivalent of what you show in your first column above. VALUE is the equivalent of what you show in your second column above. Both VARINFO and VALUE are character variables. If your N and PCTN can be in separate columns on the report, then you would not need PROC REPORT. However, if your N and PCTN must be like this: N (PCTN%) in one column, then you need to make a character variable from 2 separate numeric variables with the appropriate punctuation in the character variable (the parens and the percent sign). Once the data are restructured, I can use them as shown in the complete PROC REPORT code on pages 10 and 11 on the slides. Neither the paper, nor the slides really go into detail about how the data was restructured -- but complex1_demog.sas contains a SAS Macro program that either runs PROC FREQ or PROC MEANS, as appropriate for the desired statistics and then creates the BRKVAR, GRPORD and ROWORD variables, as well as creating the VARINFO and VALUE variables. For example the N and PCTN are created like this (assuming that you have gotten COUNT and PERCENT separately from a PROC FREQ-created dataset):

value = put(count, 3.)||' ('||put(percent, 6.2)||')';

but that is just a little snippet out of a much longer program.

When I present this paper at user group meetings, it generally takes almost an hour to get through an explanation of the macro code and the PROC REPORT code in complex1_demog.sas. If you have already built your summary data set and you have already created your columns (and although I can't see your variable names) -- which it seems from the above posting that you have already done some of the summary work, then you may be able to skip to the PROC REPORT step. However, if your data does not have BRKVAR, GRPORD and ROWORD, then you will not be able to use my code.

For example, in order to do the bolding of the headers, I have this in the code:

compute varinfo ;

if roword = 1 then do;

call define(_col_,'style',

"style={font_weight=bold}");

end;

else do;

call define(_col_,'style',

"style={font_weight=medium leftmargin=12pt}");

end;

endcomp;

Note how ROWORD variable is used to determine whethe the row should be bolded or not. That is how the bolding happens. Without ROWORD, the bolding won't happen as easily. On the ELSE branch, the LEFTMARGIN=12pt is the style attribute that indents the other values for VARINFO.

To get the "Patient Characteristics" and the N at the top -- all underlined, this section of code (using COMPUTE BEFORE _PAGE_) was how it happened. At the time I wrote the paper, the TEXTDECORATION style attribute for underlining was not available yet, so I used the \ul RTF control string to underline these 2 text strings in a COMPUTE block:

compute before _page_ / style={just=center font_size=12pt};

line "\ul{Patient Characteristics}";

line "\ul{(N= &n Patients)}";

endcomp;

To get the line "Patient Demographics" underlined, I needed to use BRKVAR, as shown in this COMPUTE block:

compute before brkvar / style={just=left font_size=10pt};

line "\ul{Patient Demographics}";

endcomp;

GRPORD is used to ensure that the report rows appear in the order I desire. (Otherwise, in my report, the string with 'CAD' would have appeared after the string 'Age' and that's not where I wanted CAD category to appear).

I believe that if you carefully compare the final dataset to the final output, especially looking at the numbered arrows on the slide PDF, and carefully examining the values for BRKVAR, GRPORD and ROWORD, you will be able to see how the bolding and indenting all happen using PROC REPORT.

Good luck with your report. If you don't quite feel comfortable with PROC REPORT, then I would recommend reviewing some basic user group papers on PROC REPORT before you jump into the complicated stuff.

cynthia