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

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

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

3. 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 ProcLabel Efficient Data Analysis with ODS Custom Formats in RTF files Applying Style Syntax in Proc Tabulate, Proc Report, and Proc Print Last Words

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

5. 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)

6. Defining Output Destinations       ODS      

7. Creating Output Objects

8. Identifying Output Objects: List File ODS TRACE ON/LABEL LISTING;

9. Object Reference by SAS Procedure PROCEDURE NAME PATH Proc_name; Obj_name Proc_name.X.Obj_name Var X; Freq OneWayFreqs Freq.X.OneWayFreqs Means Summary Means.Summary SQL SQL_Results SQL.SQL_Results Univariate Moments Univariate.X.Moments BasicMeasures Univariate.X.Basic Measures _____________________________________

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

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

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

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

14. Creating HTML Files with Style (BARRETTSBLUE)

15. Creating HTML Files with Style (BRICK)

16. Creating PDF File

17. Creating RTF Files

18. Creating Output Files with Style “SAS SUPPLIED” STYLES PURPOSE NAME Screen Display for Viewing BarrettsBlue, Beige, Brick, (HTML) D3D, Default Editing Output RTF, Minimal, Theme (RTF) Printing Output Printer,FancyPrinter (PRINTER: PDF, PS)

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

20. Creating Custom Styles using the Style Editor

21. Creating Custom Styles using the Style Editor

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

23. Creating Excel Files

24. 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’. Two Step Process: 1. Create and Save Results as Document File 2. Replay Results from Document File

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

26. Replay results with Proc Document - RTF File

27. 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;

28. Replay results with Proc Document - PDF File

29. Customize Labels with ODS ProcLabel ods html body = ‘c:\htmlbfile.htm' contents = ’c:\htmltocfile.htm' frame = ’c:\htmlffile.htm'; ODS ProcLabel '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;

30. Customize Labels with ODS ProcLabel

31. 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 (Reference to drill-down graph)

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

33. Selecting Output Objects By Name ODS HTML FILE = 'c:\select_demog.htm' ; ODS HTML SELECT BASICMEASURES; ODS HTML SHOW; PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT; RUN; ODS HTML CLOSE;

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

35. 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 UNIVARIATE.BYGROUP1.WEIGHT.BASICMEASURES UNIVARIATE.BYGROUP2.WEIGHT.BASICMEASURES; PROC UNIVARIATE DATA=DEMOG; BY DRUG; VAR WEIGHT; RUN; ODS HTML CLOSE;

36. Selecting Multiple Output Objects from BY Statements

37. Selecting Multiple Output Objects from BY Statements

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

39. Selecting Multiple Output Objects from Multiple Analysis Variables

40. Selecting Multiple Output Objects from Multiple Analysis Variables

41. Creating Output Data Sets ODS OUTPUT BASICMEASURES = MEASURE; PROC UNIVARIATE DATA=DEMOG; VAR WEIGHT; RUN; ODS OUTPUT CLOSE;

42. Creating Output Data Sets: List File * Proc print of MEASURE Output Data Set; Measure as SAS Data set Var Loc Obs Name Measure LocValue VarMeasure VarValue 1 weight Mean 198.6800 Std Deviation 44.55682 2 weight Median 199.0000 Variance 1985 3 weight Mode . Range 159.00000 4 weight _ Interquartile Range 56.00000

43.  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.

44. Creating Multiple Data Sets with the MATCH_ALL option

45. Creating Multiple Data Sets with the MATCH_ALL option

46. Creating Multiple Data Sets with the MATCH_ALL option

47. Using Traffic Lighting Conditions to identify significance

48. Using Traffic Lighting Conditions to identify significance

49. Using Traffic Lighting Conditions to identify significance

50. Creating Plots and Tables as RTF File

51. Creating Plots and Tables as RTF File

52. Creating Drill-down HTML Plots: Summary Plot

53. Creating Drill-down HTML Plots: Active Details

54. Creating Drill-down HTML Plots: Placebo Details

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

56. 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.3fs];

57. Customize RTF File: Header/Footer Sections - Titles Title font=arial bold "Table 1. Baseline Table: Sex, Race^{super a} Page ^{pageof} "; Font: font=<courier|times|arial|helvetica> <bold|median|light> <italic|roman|slant>; Height: height=8pt; Justify: j=<left|right|center>; Superscript: ^{super &text}; Subscript: ^{sub &text}; Page X of Y: ^{pageof}; "{Page} {\field{\*\fldinst{ PAGE }}} \~{of}\~{\field{\*\fldinst { NUMPAGES }}}"

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

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

60. 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-word0 "' 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.

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

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

63. 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;

64. 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.)

65. Customize RTF File: Using Page-Break Control

66. 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.

67. 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 label */ run;

68. 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<gender race>=' %' *f=pctpct. ) / box=[label='Baseline ' /* Style for box label */ style=[&mystyle]] rts=43; run;

69. Customize RTF File: Using PROC TABULATE - Table 1

70. 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<gender race>=' %' *f=pctpct. ) / box=[label='Baseline ' /* Style for box label */ style=[&mystyle]] rts=43; run;

71. Customize RTF File: Using PROC TABULATE - Table 1

72. 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;

73. Customize RTF File: Using PROC TABULATE - Table 2

74. 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 lines */ run;

75. 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;

76. Customize RTF File: Using PROC REPORT

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

78. Customize RTF File: Using PROC PRINT 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;

79. Customize RTF File: Using PROC PRINT - Table 3

80. 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.

81. SAS Books

82. 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)

83. 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

84. 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 McNeill, 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 McNeill, Ray Pass, PROC REPORT: Doin’ It in Style

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

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