ODS Basics

1
ODS Basics
Prepared by Yves Lapierre Bell Canada
2
ODS Basics

• Overview
• What is ODS ?
• ODS Statements
• Excel with ODS

3
What is ODS ?

• Output Delivery System (ODS)

Report Destinations
Listing
Printer
RTF
PDF
HTML
CSV
Excel ???
XML
Data Destination
Output
4
What is ODS ?

• ODS Objects
• Procedure output is divided into components or
  output objects.
• Some procedures may only have a single output
  object while others have multiple output objects.
• Each output object has a set of attributes, which
  includes a name and a label.
• ODS stores a link to each output object in the
  Results folder in the Results window.
• Output Added
• -------------
• Name OneWayFreqs
• Label One-Way Frequencies
• Template Base.Freq.OneWayFreqs
• Path Freq.NbCalls.OneWayFreqs
• -------------

5
What is ODS ?

• ODS Output Production

Destination
Result
SASData Set Output Window HTMLFile Postscript
File
Output Listing HTML Printer
6
ODS Statements

• ODS Statements

7
ODS Statements

• ODS Listing Statement
• The ODS LISTING statement opens, closes, or
  manages the LISTING destination. This is the
  default and no extra code is needed to use it.
• To enable LISTING output, use

ODS LISTING
To disable LISTING output, use
ODS LISTING CLOSE
To send output to an external file, use
ODS LISTING FILEfile-specification fileref
8
ODS Statements

• ODS Printer Statement
• The ODS PRINTER statement opens the printer
  destination. The ODS PRINTER CLOSE actually
  sends the output to the printer (or file).

ODS PRINTER ltSTYLEstylegt ltPRINTER'printer
name'gt ltFILE'file name'gt ltNOCOLORCOLORgt ltPOST
SCRIPTPSgt ltCLOSEgt
The output is not sent to the printer destination
until you close the ODS PRINTER statement
ODS PRINTER CLOSE
9
ODS Statements

• ODS RTF Statement
• The ODS RTF statement opens, closes, or manages
  the RTF destination.
• To create an RTF file through ODS, use

ODS RTF fileRTF-file-specification'
To disable RTF output, use
ODS RTF CLOSE
10
ODS Statements

• ODS PDF Statement
• The ODS PDF statement opens, closes, or manages
  the PDF destination
• To create an PDF file through ODS, use

ODS PDF filePDF-file-specification'
To disable PDF output, use
ODS PDF CLOSE
11
ODS Statements

• ODS HTML Statement
• The ODS HTML statement opens, closes, or manages
  the HTML destination.
• To create an HTML file through ODS, use

ODS HTML BODY'HTML-file-specification'
To close the HTML output, use
ODS HTML CLOSE
12
ODS Statements

• ODS HTML Statement

13
ODS Statements

• ODS HTML Statement
• By default, the ODS HTML statement directs the
  results from multiple procedures to the same HTML
  file.
• After the HTML destination is opened (with BODY
  or FILE option), all subsequent output is sent
  directly to the HTML destination until
• the HTML destination is disabled with an ODS
  HTML CLOSE statement
• a new ODS HTML statement is submitted with a
  different BODY file specification
• the output is rerouted using the NEWFILE
  option

14
ODS Statements

• ODS HTML Statement
• When a new file is started, a digit is appended
  to the original file name. The first file does
  not have a digit appended to its name.
• For example, the first four files created based
  on the following statement are listed below
• ods html bodyreport.html newfileproc
• 1. report.html 3. report2.html 2. report1.html
  4. report3.html
• Starting-point can be
• NONE (default) creates a single output file.
• PROC starts a new file for each new procedure
  output.
• OUTPUT starts a new file for each output object.
• PAGE starts a new file when a new page is
  explicitly generated
• BYGROUP starts a new file for each BY group

15
ODS Statements

• ODS HTML Statement
• Up to four HTML files can be specify to build a
  frame environment with ODS. Each file
  corresponds to a unique ODS HTML file type
• BODY as previously discussed, is the file which
  contains the SAS output as HTML file or files.
• CONTENTS contains the links to the file or files
  that will be loaded into the display area.
• PAGE contains links to individual pages of SAS
  output in HTML form. ODS produces a new page of
  output whenever a procedure explicitly asks for
  a new page. The SAS system option PAGESIZE has
  no effect on ODS-generated HTML output.
• FRAME contains de FRAMESET tags that organize
  the layout of the frame environment. If you
  specify the FRAME option, you must also
  specify the CONTENTS option, the PAGE option,
  or both.

16
ODS Statements

• ODS HTML Statement

Frame frame'frm.html'
Control Title
Table of Contents contents 'toc.html'
Body File body'multiple.html'
PageNumber Summary page'pg.html'
17
ODS Statements

• ODS HTML Statement

18
ODS Statements

• ODS OUTPUT Statement
• With ODS, you can create an output table
  containing every statistic in every report of
  most procedure by using the ODS OUTPUT statement.
• General form of the ODS OUTPUT statement

ODS OUTPUT output-object-specificationSAS-table
Notes PROC PRINT and PROC REPORT do not create
an output table or SAS data file using the ODS
OUTPUT statement. To create an output table from
procedures, such as MEANS, do not use the NOPRINT
option. To prevent the report from being sent to
the Output window, use ODS LISTING CLOSE instead
of NOPRINT.
19
ODS Statements
ODS OUTPUT statement ods listing close ods
output MomentsWork.Moments BasicMeasuresWork.
BasicMeasures TestsForLocationWork.TestsForLoc
ation QuantilesWork.Quantiles ExtremeObsWo
rk.ExtremeObs PROC UNIVARIATE
dataairline.personnel VAR AnnualSalary RUN o
ds listing
20
ODS Statements
ODS OUTPUT statement
21
ODS Statements

• Other ODS Statements
• ODS TRACE ONlt/option(s)gtTRACE OFF
• Writes a record to the SAS log for each output
  object that is created.
• Output Added
• -------------
• Name OneWayFreqs
• Label One-Way Frequencies
• Template Base.Freq.OneWayFreqs
• Path Freq.NbCalls.OneWayFreqs
• -------------
• ODS PROCTITLENOPROCTITILE
• Shows or suppresses the default SAS procedure
  title The Freq Procedure, The Tabulate
  Procedure, etc.
• ODS PROCLABEL
• Changes the high-level label in the PDF bookmark
  tree. Also changes the procedure label in an
  HTML contents page.

22
Excel with ODS

• Generating Excel files with ODS

XML
HTML
Excel
TEXT
CSV
23
Excel with ODS

• Generating Excel files with ODS

• CSV

• HTML

ods html filetemp.xls proc print
dataone run ods html close
ods csv filetemp.csv proc print
dataone run ods csv close

• Text

• XML

ods listing filetemp.txt proc print
dataone run ods listing close
libname temp xml C\temp.xls data
temp.one set sashelp.class run
24
Excel with ODS

• ODS HTML
• ODS HTML generates HTML files. By specifying a
  procedure or data step within the ODS HTML
  statement with the .XLS or .CSV extensions,
  Microsoft Excel is opened in the Result Viewer on
  the PC.
• ods html file'E\P000135\MONSUG\ODS
  Example.xls'
• PROC FREQ dataMONSUG.SampProf
• TABLES AvgHHInc
• RUN
• ods html close

25
Excel with ODS

• ODS HTML (Output Delivery System)

26
Excel with ODS

• Cell Formatting

• Leadings Zeroes
• Scientific Notation
• Insignificant Digits
• Dates

27
Excel with ODS

• Common Number Formats

mso-number-format"mm\/dd\/yy"
Date7 mso-number-format"mm
mm\\ d\\\,\\ yyyy"
Date9 mso-number-format"m\/d\/yy\\ h\mm\\
AM\/PM" Date-time AMPM mso-number-format"M
edium Date"
01-mar-98 mso-number-format"d\\-mmm\\-yyyy"
01-mar-1998 mso-numb
er-format"Short Time"
516 mso-number-format"Medium
Time"
516 am mso-number-format"Long Time"

5162100 mso-number-format"0\.000"
3
Decimals mso-number-formatPercent

Percent mso-number-format0
No
percent mso-number-format"0\.E00"

Fractions mso-number-format"\_at_"
Text
Dates
Other
28
Excel with ODS

• Text format applied to all columns

ods html filetemp.xls headtext ltstylegt td
mso-number-format\_at_lt/stylegt PROC PRINT
dataone var acct_no zipcode
RUN ods html close
Text format applied to a single column
ods html filetemp.xls headtext
ltstylegt.zero mso-number-format\_at_lt/stylegt
PROC PRINT dataone VAR acct_no /
stylehtmlclasszero VAR zipcode
RUN ods html
close
29
Excel with ODS

• Frequency with ODS PHTML
• ODS PHTML and CHTML are experimental in Version
  8.2
• ods phtml file'E\P000135\MONSUG\ODS PHTML
  Example.xls'
• / Number Format to 3 decimals /
• headtext "ltstylegttd mso-number-format'0\.000'lt
  /stylegt"
• ods noptitle / Take out the procedure title /
• / Take out the Wrap-Text property for the Title
  cell /
• title 'ltdiv style"white-spacenowrap"gt lt/divgt'
• PROC FREQ dataMONSUG.SampProf
• TABLES AvgHHInc
• FORMAT AvgHHInc Income.
• TITLE Average Household Income for Sample A'
• RUN
• ods phtml close

30
Excel with ODS
Frequency with ODS PHTML
31
Excel with ODS
PROC EXPORT PROC EXPORT and the Export Wizard are
useful tools for exporting SAS Data Sets to
spreadsheets. PROC EXPORT data
MONSUG.DataProfile OUTFILE
"E\P000135\MONSUG\MONSUG Example.xls"
DBMSEXCEL2000 REPLACE sheet
'Profile' RUN In Version 9, you can write to
specific sheet in an Excel Workbook Note This
is available for Excel 97, 2000 and 2002 under
Windows and assumes you have SAS/ACCESS licensed
for PC File Formats.
32
Excel with ODS

• Output a procedure in a SAS Dataset
• SAS outputs in a dataset the frequency and
  creates two variables one with original values
  and the other with the formatted values.
• ods output OneWayFreqs AvgIncFreq
• (KEEP F_AvgHHInc Frequency RENAME(F_AvgHHIncAv
  gHHIncome))
• PROC FREQ dataMONSUG.SampProf
• TABLES AvgHHInc
• FORMAT AvgHHInc Income.
• RUN
• PROC EXPORT data AvgIncFreq
• OUTFILE "E\P000135\MONSUG\Average Income
  Example.xls"
• DBMSEXCEL2000 REPLACE
• sheet 'Raw Data'
• RUN

33
Excel with ODS
Output a procedure in a SAS Dataset
34
Thank You

• Questions

• Comments