Reporting 101: REPORT, TABULATE, ODS, and Microsoft Office

1
Reporting 101 REPORT, TABULATE, ODS, and
Microsoft Office

• Lauren Haworth
• Genentech, Inc.
• South San Francisco, CA

2

• Fasten your seatbelts!
• This paper is a quick tripthrough your SAS
  reporting options

3
Topics

• The REPORT procedure
• The TABULATE procedure
• Which is best to use for what types of reports?

4
Topics

• Using the Output Delivery System to create
• web page
• PDF file
• Word document
• PowerPoint slide
• Excel spreadsheet

5
The Basic Procedures

• PRINT, MEANS, AND FREQ

6
PROC PRINT

• proc print datacensusvar sex educ income
• run

7
PROC MEANS

• proc means datacensus
• var income
• run

8
PROC FREQ

• proc freq datacensus
• tables sex educ
• run

9
PROC REPORT
10
PROC REPORT syntax

• Very similar to PROC PRINT
• Instead of a VAR, use COLUMNS
• Add NOWD to run in batch
• proc report datacensus nowd columns educ
  sex incomerun

11
PROC REPORT output

• Just like PROC PRINT
• Need to add options to make it more useful

12
DEFINE statement

• provides further instructions on how to display
  each variable
• proc report datacensus nowd columns educ
  sex income define educ / 'Education'
  group
  orderinternalrun

Column label
Request grouped results
Sort by data values, not formatted values
13
DEFINE statement output
14
Another DEFINE statement

• provides further instructions on how to display
  each variable
• proc report datacensus nowd columns educ
  sex income define educ / 'Education'
  group
  orderinternal define sex / 'Gender'
  grouprun

15
Revised output
What is this number?
16
Another DEFINE statement

• provides further instructions on how to display
  each variable
• proc report datacensus nowd columns educ
  sex income define educ / 'Education'
  group
  orderinternal define sex / 'Gender' group
  define income / 'Salary'
  mean formatdollar8.run

Statistic to compute
Format for display
17
Revised output
18
To Learn More About REPORT

• See these papers in the SUGI 28 proceedings
• So youre still not using the REPORT Procedure.
  Why Not? by Ray Pass
• Getting Up to Speed with PROC REPORT, by
  Kimberly LeBouton
• (Note you dont need to rush to write these
  down. These papers and all other references are
  listed in my paper in the proceedings)

19
PROC TABULATE
20
PROC TABULATE syntax

• Syntax very different from other PROCs
• Starting with the simplest possible report
• proc tabulate datacensus var income
  table incomerun

Describes the variable(s)
Requests a table
21
TABULATE output
22
CLASS statement

• proc tabulate datacensus var income
• class educ table educ,
• incomerun

Describes the numeric variable(s)
Describes the category or grouping variable(s)
Requests a 2-D table
23
Revised output
24
Multiple CLASS variables

• proc tabulate datacensus var income
• class sex educ table educ,
• income sexrun

25
Revised output
26
Selecting the Statistic

• proc tabulate datacensus var income
• class sex educ table educ,
• incomemean sexrun

27
Revised output
28
Adding Labels

• proc tabulate datacensus var income
• class sex educ table educ'Education',
• income'Average Salary'
  mean' ' sex' 'run

29
Revised output
30
Adding Totals

• proc tabulate datacensus var income
• class sex educ table educ'Education',
• income'Average Salary'
  mean' ' (sex' ' all)
• run

31
Revised output
32
Formatting the Results

• proc tabulate datacensus fdollar8. var
  income
• class sex educ table educ'Education',
• income'Average Salary'
  mean' ' (sex' ' all)
• run

33
Revised output
34
To Learn More About TABULATE

• In the SUGI 28 proceedings
• The Simplicity and Power of the TABULATE
  Procedure, by Dan Bruns
• Online (from the SUGI 27 proceedings)
• Anyone Can Learn PROC TABULATE, by Lauren
  Haworth, www2.sas.com/proceedings/sugi27/p060-27.p
  df

35
REPORT vs. TABULATE

• Picking the right PROC for the job

36
REPORT is best for

• Reports with lots of text.
• FLOW option allows you to wrap text

37
REPORT is best for

• Reports with row headings of varying width.
• WIDTH option sets the width of each column.
• The row heading columns in TABULATE have to be
  equal width.

38
REPORT is best for

• Reports that need customized summaries on each
  page, or each subsection.
• The COMPUTE block and the LINE statement allow
  you to calculate and display summary statistics
  and text messages.

39
TABULATE is best for

• Complex cross-tabulations.
• TABULATE has a lot more flexibility.

40
TABULATE is best for

• Tables with multiple statistics displayed in the
  rows

41
To Learn More on REPORT vs. TABULATE

• In the SUGI 28 proceedings
• Report vs. Tabulate - The Battle in Seattle! by
  Ray Pass and Dan Bruns

42
Sending results to the Web
43
Using ODS to create a web page

• Requires SAS version 8 or later
• Need to add two lines of code
• ODS HTML FILEmyfilename.html
• your reporting procedure(s) go here
• ODS HTML CLOSE

44
Web Page results
This is the TABULATE output from earlier in the
presentation
45
Changing the Style

• Dont like the design?
• Change the style
• ODS HTML FILEmyfilename.html
  STYLEBarrettsBlue
• your reporting procedure(s) go here
• ODS HTML CLOSE

46
Web Page results
47
For more information on HTML

• See my paper,HTML for the SAS Programmer,
  available at http//www.laurenhaworth.com/pubs_cur
  rent.htm

48
Creating Reports to Import Into Word
49
Using ODS to create RTF

• RTFRich Text Format
• Can be read by Word
• Need to add two lines of code
• ODS RTF FILEmyfilename.rtf
• your reporting procedure(s) go here
• ODS RTF CLOSE

50
Results in Word
This is the REPORT output from earlier in the
presentation
51
ODS RTF and Word issue

• By default, the RTF output has margins of .25
  when you open it in Word.
• This is generally too small
• To fix this, you can
• Reset the margins after opening the document in
  Word.
• Customize your ODS style to fix the margins. For
  instructions on how to do this, see the example
  on RTF margins in my paper ODS Tips Tricks,
  available at http//www.laurenhaworth.com/pubs_cur
  rent.htm

52
For more information on RTF

• Now There Is an Easy Way to Get to Word, Just
  Use PROC TEMPLATE, PROC REPORT, and ODS RTF, by
  Bob Hull, at www2.sas.com/proceedings/sugi26/p163-
  26.pdf
• To ODS RTF and Beyond, by David Shannon, at
  www2.sas.com/proceedings/sugi27/p001-27.pdf

53
Sending results to PDF
54
Using ODS to create PDF

• Can be viewed on many platforms, using a free
  viewer
• Your report will print out easily on a variety of
  printers, with correct margins and page breaks.
• Need to add two lines of code
• ODS PDF FILEmyfilename.pdf
• your reporting procedure(s) go here
• ODS PDF CLOSE

Note this is v8.2 code, see paper for v8.0 and
v8.1 version
55
Results in PDF
This is the same REPORT output as the previous
example Notice how similar it is to the RTF
output
56
Sending results to Excel
57
ODS EXCEL?

• No, its not quite that easy
• Since Excel can read HTML files, the trick is to
  use ODS HTML
• ODS HTML FILEmyfile.xls your reporting
  procedure(s) go hereODS HTML CLOSE

This is the trick to Excel output
58
The Excel results

• Excel converts file automatically.
• Use Save to preserve the conversion

59
Sending results to PowerPoint
60
Sending results to PowerPoint

• Like Excel, PowerPoint can understand HTML output
• ODS HTML is again the solution
• ODS HTML FILEmyfile.html your reporting
  procedure(s) go hereODS HTML CLOSE

61
PowerPoint results

• You can open the HTML file from PowerPoint
• For a single table, this works well
• For multiple tables, all are crammed onto a
  single slide
• Resizing is difficult

62
PowerPoint results

• A better approach
• Open browser and PowerPoint side-by-side
• Cut and paste tables from the web output to
  individual slides

63
Conclusions

• You now have a basic idea of what SAS reporting
  options are available to you.
• The intent was to give you a taste of each
  procedure and tool, and references to additional
  information

64
Conclusions

• In the future, when you need to design a new
  report, this gives you a starting point.
• As you start to use these techniques, be sure to
  explore
• the SAS documentation
• the SUGI papers referenced previously

65
Thanks for Coming!

• Any Questions?