SAS s ODS Technology for Today s Decision Makers Sunil

SAS®’s ODS Technology for Today’s Decision Makers Sunil Gupta, Gupta Programming

SAS Users Programmer: HTML, PDF, RTF Statistician: p-values, plots Manager: Excel file, HTML drill down files

Agenda Ø What’s Unique about ODS? Ø Creating Custom Styles using the Style Editor Ø Creating Excel Files Ø Replay Results with Proc Document Ø Customize Labels with ODS Proc. Label Ø Efficient Data Analysis with ODS Ø Custom Formats in RTF files Ø Applying Style Syntax in Proc Tabulate, Proc Report, and Proc Print Ø Last Words

What’s Unique about ODS? DOOR # 1 Create Files DOOR # 2 Control Output

What’s Unique about ODS? Destinations (Defines the output file type: HTML, RTF, PDF, LISTING, OUTPUT) Creating Output Objects (Reference to Output Object Names) Creating Output Files with Style (Reference to ‘SAS-Supplied’ Styles)

Defining Output Destinations Standard: SAS List HTML RTF ODS Output: SAS Dataset Printer: PS, PDF PCL

Creating Output Objects Results of PROC UNIVARIATE O U T P U T O B J E C T S Moments Basic. Measures Tests. For. Location Quantiles Extreme. Obs

Identifying Output Objects: List File ODS TRACE ON/LABEL LISTING; (One of Several Output Objects Created) Name: Basic. Measures Label: Basic Measures of Location and Variability Template: base. univariate. Measures Path: Univariate. weight. Basic. Measures Label Path: 'The Univariate Procedure'. 'weight'. 'Basic Measures of Location and Variability'

Object Reference by SAS Procedure PROCEDURE NAME PATH Proc_name; Obj_name Proc_name. X. Obj_name Var X; Freq One. Way. Freqs. Freq. X. One. Way. Freqs Means Summary Means. Summary SQL SQL_Results SQL_Results Univariate Moments Univariate. X. Moments Basic. Measures Univariate. X. Basic

Creating Output Files with Style Enhance report using predefined color, font, size, . . Easy to use “SAS-Supplied” Styles are available with installation

Creating Output Files with Style STYLE SUPPORT BY DESTINATION SUPPORT DESTINATION STYLE Listing No Printer Yes RTF Yes Data Set No HTML Yes

Creating Output Files: Report Files ODS File = ’ ’; SAS Procedure(s); ODS CLOSE; Where File Type = HTML, RTF, Listing, PDF, . .

Creating HTML Files with Style ODS HTML FILE = 'c: demog_style. htm' STYLE = barrettsblue; PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT; RUN; ODS HTML CLOSE;

Creating HTML Files with Style (BARRETTSBLUE)

Creating HTML Files with Style (BRICK)

Creating PDF File

Creating RTF Files

Creating Output Files with Style “SAS SUPPLIED” STYLES PURPOSE NAME Screen Display for Viewing Barretts. Blue, Beige, Brick, (HTML) D 3 D, Default Editing Output RTF, Minimal, Theme (RTF) Printing Output (PRINTER: PDF, PS) Printer, Fancy. Printer

Creating Custom Styles using the Style Editor in Enterprise Guide Demo Tools Style Editor HTML Click on preview item to select a style element (System Title, Proc Title, Table, Header, Row Header, Data, System Footer) n Text tab: font, font style, size, color, alignment n Border tab: attributes, color, style, width, margin, padding n Images, Custom tabs n Save as (new style) n Tools/Options/Results/Style (to set as default style) EG or Learning Edition great for non-programmers

Creating Custom Styles using the Style Editor

Creating Custom Styles using the Style Editor

Creating Excel Files ODS HTML FILE = 'c: demog. xls’ style = minimal; PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT; RUN; ODS HTML CLOSE; Excel

Creating Excel Files

Replay results with Proc Document ODS Document - enables users to render, with a single run, multiple ODS output formats to one or more ODS destinations without rerunning a SAS procedure or Data Step. Produces a hierarchy of output objects that can be ‘replayed’. Data Content Data Presentation Two Step Process: 1. Create and Save Results as Document File 2. Replay Results from Document File

Replay results with Proc Document Step 1: Create and Save Results as Document File ODS Document name = myresults ODS RTF file = ‘c: rtffile. rtf’; Proc Freq data=demog; tables gender; run; ODS RTF CLOSE; (update);

Replay results with Proc Document RTF File

Replay results with Proc Document Step 2: Replay Results from Document File ODS PDF file = ‘c: pdffile. pdf’ style = barrettsblue; Proc Document name = myresults (update); replay; run; ODS PDF CLOSE;

Replay results with Proc Document PDF File

Customize Labels with ODS Proc. Label ods html body = ‘c: htmlbfile. htm' contents = ’c: htmltocfile. htm' frame = ’c: htmlffile. htm'; ODS Proc. Label 'This is my FREQ label'; /* Replaces the default label - The Freq procedure */ /* ODS NOPTITLE - to remove title */ Proc Freq data=demog; tables gender; run; ODS html CLOSE;

Customize Labels with ODS Proc. Label

Efficient Data Analysis with ODS Selecting Objects Creating Output Data sets Creating Multiple Data sets with the MATCH_ALL option Using Traffic Lighting conditions to identify significance Creating Plots and Tables as RTF file

Options in Selecting Output Objects By Object Name By Object Path Name Each Destination All Destinations

Selecting Output Objects By Name ODS HTML FILE = 'c: select_demog. htm' ; (Object Name) ODS HTML SELECT BASICMEASURES; /* ODS HTML SHOW; Default is selecting ALL Objects */ PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT; RUN; ODS HTML CLOSE;

Other Factors to Consider By Statements Multiple Analysis Variables Selection Lists Multiple Destinations

Selecting Multiple Output Objects from BY Statements PROC SORT DATA=DEMOG; BY DRUG; RUN; ODS HTML FILE=’c: select_weight_demog. htm’; ODS HTML SELECT /* ODS HTML SELECT BASICMEASURES; */ UNIVARIATE. BYGROUP 1. WEIGHT. BASICMEASURES UNIVARIATE. BYGROUP 2. WEIGHT. BASICMEASURES; PROC UNIVARIATE DATA=DEMOG; BY DRUG; VAR WEIGHT; RUN; ODS HTML CLOSE;

Selecting Multiple Output Objects from BY Statements

Selecting Multiple Output Objects from BY Statements

Selecting Multiple Output Objects from Multiple Analysis Variables ODS HTML FILE= ’c: select_weight_height_demog. htm’; ODS HTML SELECT /* ODS HTML SELECT BASICMEASURES; */ UNIVARIATE. WEIGHT. BASICMEASURES UNIVARIATE. HEIGHT. BASICMEASURES; PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT HEIGHT; RUN; ODS HTML CLOSE;

Selecting Multiple Output Objects from Multiple Analysis Variables

Selecting Multiple Output Objects from Multiple Analysis Variables

Creating Output Data Sets (Object Name) (Data set Name) ODS OUTPUT BASICMEASURES = MEASURE; PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT; RUN; ODS OUTPUT CLOSE;

Creating Output Data Sets: List File * Proc print of MEASURE Output Data Set; Measure as SAS Data set Obs Var Name 1 2 3 4 weight Loc Measure Loc. Value Var. Measure Var. Value Mean 198. 6800 Std Deviation 44. 55682 Median 199. 0000 Variance 1985 Mode. Range 159. 00000 _ Interquartile Range 56. 00000

Creating Output Data Sets Works with any SAS Procedure All results are available in the data set Options are available for creating multiple data sets Note: Requires having information about the object. Can use the ODS TRACE statement to get this information.

Creating Multiple Data Sets with the MATCH_ALL option ODS OUTPUT BASICMEASURES /* object name */ (MATCH_ALL = MEASURE_DSN /* macro name */ PERSIST = PROC) /* maintain selection list */ = MEASURE; /* data set names - measure, measure 1, measure 2 */ PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT HEIGHT; RUN; PROC UNIVARIATE DATA=DEMOG; VAR AGE; RUN;

Creating Multiple Data Sets with the MATCH_ALL option 101 102 103 PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT HEIGHT; RUN; NOTE: The data set WORK. MEASURE has 4 observations and 7 variables. NOTE: The data set WORK. MEASURE 1 has 4 observations and 7 variables. NOTE: PROCEDURE UNIVARIATE used (Total process time): real time 1. 92 seconds cpu time 0. 05 seconds 104 105 PROC UNIVARIATE DATA=DEMOG; VAR AGE; RUN; NOTE: The data set WORK. MEASURE 2 has 4 observations and 7 variables. NOTE: PROCEDURE UNIVARIATE used (Total process time): %put measure_dsn; measure 1 measure 2

Creating Multiple Data Sets with the MATCH_ALL option data allmeans; set &measure_dsn; /* set measure 1 measure 2; */ where locmeasure = 'Mean'; keep varname locmeasure locvalue; run; Obs 1 2 3 Var Name weight height age Loc Measure Mean Loc. Value 198. 6800 66. 8400 56. 5280

Using Traffic Lighting Conditions to identify significance proc format; value traffic low - 99 = 'cx 006600' /* green */ 100 - high = 'cx. FF 9900'; /* orange */ quit; ods html file = ’c: print_results. htm'; proc report data=allmeans noheader nowd; columns varname locvalue; define locvalue /style = {background = traffic. }; run; ods html close;

Using Traffic Lighting Conditions to identify significance

Using Traffic Lighting Conditions to identify significance Points to Remember Create format with color labels ex. 'cx 006600’. (Lauren Haworth, SAS with Style: Creating your own ODS Style Template) Works best for HTML destination. Best to use for background color. Selected procedures - Print, Report, Tabulate.

Creating Plots and Tables as RTF File ods rtf file = ’c: plots. rtf’; proc univariate data = demog plots; var weight; run; ods rtf close; Plots. rtf

Creating Plots and Tables as RTF File

Creating Drill-down HTML Plots: Summary Plot Graph. htm

Creating Drill-down HTML Plots: Active Details

Creating Drill-down HTML Plots: Placebo Details

Customize RTF File ODS ESCAPECHAR = ‘^’; In-Line Formatting Italic, Size, Superscript, Subscript, RTF control words Ø Font, Bold, Header/Footer sections Ø Titles and Footnotes, Page X of Y Page-Break Control Using PROC TABULATE, REPORT and PRINT Ø Style options

Customize RTF File: In-Line Formatting as Style Options Font, Bold, Italic Style=[font_face=‘arial’]; Style=[font_weight=bold]; Style=[font_style=italic]; Size Style=[font_size=1. 3 fs]; Face Weight Style

Customize RTF File: Header/Footer Sections - Titles Title font=arial bold "Table 1. Baseline Table: Sex, Race^{super a} Page ^{pageof} "; Face Font: font= Weight Style ; Height: height=8 pt; Justify: j=; (Print Preview/Print) Superscript: ^{super &text}; Subscript: ^{sub &text}; Page X of Y: ^{pageof}; "{Page} {field{*fldinst{ PAGE }}} ~{of}~{field{*fldinst { NUMPAGES }}}"

Customize RTF File: Header/Footer Sections - Footnotes Footnote 1 ”^S={font_face=arial} ^{super a}Race: Non -White consists of Black, Hispanic, and Native American^S={} "; Footnote 2 ”^S={font_face=arial font_style=italic}Program: /stat/druga/program 1. sas ^S={}" ; Font: Face “^S={font_face=arial}&text ^S={}”; Weight “^S={font_weight=bold}&text ^S={}”; Style “^S={font_style=italic}&text ^S={}”;

Customize RTF File: RTF Control Words Effect Italics underline bullet bold subscript superscript strike Control Word i ul bullet b super strike (http: //msdn. microsoft. com/library/default. asp? url=/library/en -us/dnrtfspec/html/rtfspec. asp)

Customize RTF File: RTF Control Words Understanding the Rules 1. Carrot ‘^’ symbol is best as the escapechar symbol since the RTF syntax uses the symbol. 2. Use the following formula and conditions: "text" || '^R"rtf-control-word raw-text rtf-control-word 0 "' a. Double quotes around text strings. b. Concatenate all strings with || operator. c. Use single quote (to set ^R) and then double quotes around rtf-control-word string (rtf-control-word and affected text). d. Close each rtf-control-word string with 0, double then single quotes.

Customize RTF File: RTF Control Words - Title & Data Step ods escapechar = '^'; title 1 '^R/RTF"ul " Title contains underlined text'; data rtfcontrol; a = "The text uses RTF control words " || '^R"i italic text i 0 "' || " regular text " || '^R"ul underlined text ul 0 "’ || '^R"strike text strike 0 "’ ; run; ods rtf file = ’c: rtf_control. rft'; proc print data=rtfcontrol; run; ods rtf close;

Customize RTF File: RTF Control Words - Title & Data Step RTF

Customize RTF File: Settings OPTIONS ORIENTATION=PORTRAIT nodate center nonumber; ODS ESCAPECHAR = ’^'; ODS RTF FILE = 'C: baseline_tables. rtf' STYLE = MINIMAL /* Basic MS Word table, no color */ BODYTITLE; /* Keep titles and footnotes in body */ SAS Procedure(s) ODS RTF CLOSE;

Customize RTF File: Page-Break Control STARTPAGE option ODS RTF FILE = 'C: baseline_tables. rtf' STYLE = MINIMAL BODYTITLE STARTPAGE = NO; /*Prevents new page at the start of each SAS Procedure */ (Warning: You may lose titles and footnotes. )

Customize RTF File: Using Page-Break Control

Note about Page-Breaks ODS Tip to prevent blank extra pages Multiple procedures (ex, PROC SQL) to select observations of bad data. Good data results in zero observations. By default, a blank page is created for each zero observation. The non-zero observations were barred within all of the extra blank pages. LISTING, PS and PDF destinations support non-blank pages. HTML and RTF destinations do not support non-blank pages.

Applying Style Syntax in Proc Tabulate PROC TABULATE style=[]; /* Style for all data cells */ class /style=[]; /* Style for column and row header */ var / style=[]; /* Style for analysis variable heading */ table style=[] /* Style for entire table */ / box=[style=[]] ; /* Style for box

Customize RTF File: Using PROC TABULATE - Table 1 %let mystyle = %str(font_face="arial” font_weight=bold); PROC TABULATE data=demog missing formchar=' ------' style=[font_face=”courier"]; /* Style for all data */ /* Style for column and row header */ class gender race drug/style=[&mystyle]; table (gender race), drug=' '* (n='N'*f=7. pctn=' %' *f=pctpct. ) / box=[label='Baseline ' /* Style for box label */

Customize RTF File: Using PROC TABULATE - Table 1 Arial Courier New Table 1

Customize RTF File: Using PROC TABULATE - Table 1 PROC TABULATE data=demog missing formchar=' ------' style=[font_face=”courier"]; /* Style for all data */ /* Style for column and row header */ class gender race drug/style=[&mystyle]; classlev gender race drug/style=[&mystyle]; keyword n pctn /style=[&mystyle]; table (gender race), drug=' '* (n='N'*f=7. pctn=' %' *f=pctpct. ) / box=[label='Baseline ' /* Style for box label */

Customize RTF File: Using PROC TABULATE - Table 1 Arial

Customize RTF File: Using PROC TABULATE - Table 2 PROC TABULATE data=demog missing formchar=' -----' style=[font_face=" courier "]; class drug/style=[&mystyle]; classlev drug/style=[&mystyle]; var age height weight/style=[font_face=”times" font_weight=bold]; keyword n mean min std max/style=[&mystyle]; table age = 'Age'*(n='N'*f=15. mean='Mean'*f=15. 1 std='STD'*f=4. 2 min='Min'*f=4. 1 max='Max'*f=4. 1) (repeat above for height and weight) , drug=' ' / box=[label='Baseline ' style=[&mystyle]] rts=53; run;

Customize RTF File: Using PROC TABULATE - Table 2 Arial Times Courier New Table 2

Applying Style Syntax in Proc Report PROC REPORT style()=[]; /* one or more styles */ (column, header, lines, report, summary) /* all cells, all headers, lines, report structure, summary */ define /style()=[]; (column, header) /* column cell or header */ break / style(summary)=[]; /* summary

Applying Style Syntax in Proc Report PROC REPORT data = demog nowd style(header column)=[font_face=‘arial’]; column patient drug gender race age height weight; define patient / style(header column) =[font_face = times font_weight=bold]; run;

Customize RTF File: Using PROC REPORT Times Arial

Customize RTF File: Using PROC PRINT style() = [] (header, data, obshead, table) /* all headers, data, obs column header, data table */ var / style() = []; (data, header) /* separate var statements */ id / style() = []; (data, header) sum / style() = []; (data, header, total) run;

Customize RTF File: Using PROC PRINT data=demog (obs=5) noobs style(header) = [font_face="arial" font_weight=bold] /* Style for headers */ style(data)= [font_face="arial"]; /* Style for all data */ var patient drug gender race age height weight; run;

Customize RTF File: Using PROC PRINT - Table 3 Arial

Last Words Take advantage of all ODS features to simplify your live. Take time to learn the style syntax and understand the various options to customize output. Column headers may wrap incorrectly in RTF file Never use OPTIONS NOPRINT to suppress listing output because this closes all ODS destinations. Confirm all hyperlinks in HTML files. Use RUN; or QUIT; statements at the end of every data step or procedure to assure that ODS statements are correctly associated.

SAS Books

SAS Training Classes Customized Reports with SAS Output Delivery System Sharpening Your SAS Skills Preparing the SAS Software Programming Environment for Regulatory Submission Sharpening Your SAS Programming Techniques (new)

SAS Books Quick Results with the Output Delivery System Sunil Gupta Output Delivery System: The Basics Lauren Haworth The Complete Guide to the SAS Output Delivery System Version 8 http: //www. sas. com/rnd/base/index-ods-resources. htm

SUGI Papers Chevell Parker, Generating Custom Excel Spreadsheets Using ODS Paul Hamilton, ODS to RTF: Tips and Tricks Dan O’Connor, Next Generation Data _NULL_ Report Writing Using ODS OO Features Brian Schellenberger, ODS LAYOUT: Arranging ODS Output as You See Fit, Presentation Quality tabular output via ODS Sandy Mc. Neill, Changes & Enhancements for ODS by Example (Through version 8. 2), What’s New in the Output Delivery System, version 9. 0 Susan and Lora, ODS for Reporting with Style Sandy Mc. Neill, Ray Pass, PROC REPORT: Doin’ It in

Summary Programmer, Statistician, Manager Power & Flexibility Customizing Output Style Editor Creating Excel Files Proc Document, ODS Proc. Label, RTF-control words Applying Style Syntax in Proc Tabulate, Proc Report, and Proc Print

SAS®’s Technoligy for Today’s Decision Makers ODS Thank you for your attention! Sunil Gupta http: //www. Gupta. Programming. com