Docstoc

Customized Reports with SAS Output Delivery System ODS

Document Sample
Customized Reports with SAS Output Delivery System ODS Powered By Docstoc
					SAS® ODS Technology
 for Today’s Decision
       Makers


      Sunil Gupta
        Quintiles


                        1
                SAS Users

Programmer: HTML, PDF, RTF



Statistician: p-values, plots


Manager: Excel file, HTML drill down files


                                             2
                   Agenda
 What‟s Unique about ODS?

 Creating Excel Files

 Efficient Data Analysis with ODS

 Custom Formats in RTF files

 Applying Style Syntax in Proc Tabulate and Proc
  Print

                                                    3
     What’s Unique about ODS?
 Destinations (Remember to turn off ODS)
   (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)

                    Part of SAS/Base
                                                      4
Defining Output Destinations


Standard: SAS List          HTML, Excel



                     ODS       RTF



 Output: SAS Dataset       Printer: PS, PDF
                                   PCL

                                              5
 Create Demog Data Set
Patient, Sex, Height, Weight, Age, Race, Drug
                (n=25 records)




                                                6
Creating Output Files: Report Files
 ODS <File Type> File = ‟ ‟;

                                            Results of
 SAS Procedure(s);                         one or more
                                         SAS Procedures

 ODS <File Type> CLOSE;



 Where File Type = HTML, RTF, Listing, PDF, ..

    Keep consistent filename types: .html, rtf, lst, pdf, ..   7
 Example 1a. Create Excel File –
           demog.xls
< ODS Statement with minimal style>

PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;

< ODS Statement >




                                      8
   Example 1a. Create Excel File

ODS HTML FILE = 'c:\how\gupta\demog.xls‟
          STYLE = minimal;

PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;
                                          Excel
ODS HTML CLOSE;

Try this in the office - Undocumented Feature.
See SAS paper on Excel and ODS. (HTML, .xls)
                                                  9
Example 1a. Create Excel
     File: demog.xls




             Each result item is in it’s
             own cell!




                                           10
Example 1b. Create Demog_Style
     HTML File with Style

< ODS HTML Statement with barrettsblue style >



PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;



< ODS HTML Statement >

                                                 11
  Example 1b. Create HTML File
           with Style

ODS HTML FILE = 'c:\how\gupta\demog_style.html'
          STYLE = barrettsblue;



PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;



ODS HTML CLOSE;
                                                  12
Example 1b. Create HTML File
 with Style (BARRETTSBLUE)




                               13
Creating Output Files with Style
“SAS Supplied” Styles (partial list)

NAME           DESCRIPTION
BarrettsBlue Blue header background, light table
              background
Beige         Beige header text, white text in
              table
Brick         Brick color header text, white text in table
Brown         Brown title, black header, light table
              background
…
    (see Quick Results with the Output Delivery System book)
             Proc Template; list styles; run;
                                                        14
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
                                                  15
Example 2a. Create Output Data
   Set Measure from Basic
        Measures Info

ODS OUTPUT <object name> = <data set name>;

PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;

ODS OUTPUT CLOSE;



                                              16
   Creating Output Objects


               O
               U   Moments
               T
               P
               U   BasicMeasures
  Results of   T
                   TestsForLocation
PROC           O
UNIVARIATE     B
               J   Quantiles
               E
               C
               T   ExtremeObs
               S
                                      17
Identifying Output Objects: List File

            ODS TRACE ON / LABEL LISTING;
          (One of Several Output Objects Created)
 Output Added:

  Name:        BasicMeasures
  Label:       Basic Measures of Location and
  Variability
  Template:    base.univariate.Measures*
  Path:        Univariate.weight.BasicMeasures
  Label Path: 'The Univariate Procedure'.'weight'.'Basic
               Measures of Location and Variability'

  * Except for: Proc Print, Proc Tabulate and Proc Report. 18
Example 2a. Create Output Data
   Set Measure from Basic
        Measures Info
ODS TRACE ON / LABEL LISTING;
*ODS OUTPUT <object name> = <data set name>;

PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;

ODS OUTPUT CLOSE;


                                               19
Example 2a. Create Output Data
   Set Measure from Basic
        Measures Info
              (Object Name) (Data set Name)
ODS OUTPUT BASICMEASURES = MEASURE;

PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT;
RUN;

ODS OUTPUT CLOSE;
Proc print; run;
Note: OUTPUT OUT = statement in procedures are still
  valid!                                       20
Example 2a. Create Output Data Set
             Measure

* 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


          Get comfortable with the output data set.
                                                         21
       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.

                                                          22
  Example 2b. Create 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, measure1, measure2 */
PROC UNIVARIATE DATA=DEMOG;
VAR WEIGHT HEIGHT; RUN;
PROC UNIVARIATE DATA=DEMOG;
VAR AGE; RUN;
ODS OUTPUT CLOSE;
%PUT Macro variable = &MEASURE_DSN;                    23
Example 2b. Create Multiple Data
Sets with the MATCH_ALL option
data allmeans;
 set &measure_dsn;
 /* set measure measure1 measure2; */
  where locmeasure = 'Mean';
  keep varname locmeasure locvalue;
run;
                Var       Loc
         Obs    Name    Measure    LocValue
         1     weight   Mean      198.6800
         2     height   Mean       66.8400
         3     age      Mean       56.5280    24
 Example 3. Using Traffic Lighting
    low = green, high = orange
proc format;
 value traffic low - 99 = ‘ ’
           100 - high = ‘ ’ ;
quit;
ods html file = ‘c:\how\gupta\print_results.htm’;
proc report data=allmeans nowd;
 columns varname locvalue;
 define varname/ ‘Variable’;
 define locvalue / ‘Mean’
       style = {background = <format>};
run; ods html close;
                                                    25
 Example 3. Using Traffic Lighting
    low = green, high = orange
proc format;
 value traffic low - 99 = ‘green’ /* cx006600 */
           100 - high = ‘orange’ /* cxFF9900 */ ;
quit;
ods html file = ‘c:\how\gupta\print_results.htm’;
proc report data=allmeans nowd;
 columns varname locvalue;
 define varname/ ‘Variable’;
 define locvalue / ‘Mean’
       style = {background = traffic.};
run; ods html close;
                                                26
Using Traffic Lighting Conditions
     to identify significance




                                             Greater
                                             Than
                                             100




        Make results visually informative.         27
     Example 4. Create Plots and
       Tables as Plots.rtf File

< ODS Statement to create rtf file >;

proc univariate data = demog < plots >;
 var weight;
 < histogram / cfill=cyan>;
run;

ods rtf close;
                                        28
     Example 4. Create Plots and
       Tables as Plots.rtf File

ods rtf file = ’c:\how\gupta\plots.rtf’;

proc univariate data = demog plots;
 var weight;
 histogram / cfill=cyan;
run;
                                               Plots.rtf

ods rtf close;
     You can have it all - tables and plots in one file!
                                                           29
Example 4. Creating Plots and
     Tables as RTF File




                                30
            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)
                                                            31
               Customize RTF File:
               RTF Control Words
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 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 quote to start
      rtf-control-word string (“\rtf-control-word).
   d. The text to be affected.
   e. Repeat and close each rtf-control-word string with 0, double
      quote (“) then single quote („), (\rtf-control-word0 “‟). 32
      Example 5. Custom RTF File: RTF
    Control Words - Title and 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:\how\gupta\rtf_control.rtf';
proc print data=rtfcontrol;
run; ods rtf close;
                                          Be careful
                                          to match
                                          all quotes   33
  Example 5. Custom RTF File: RTF
Control Words - Title and Data Step




                                            RTF
  Look what SAS can do - it really works!     34
Example 6. Summary Table Using In-
 line Formatting Text in RTF files
OPTIONS ORIENTATION=PORTRAIT nodate center;
ODS ESCAPECHAR = „^‟;                      /* required */
  /* signals start of non SAS code - tags ex. {super a}*/

ODS RTF FILE = 'C:\how\gupta\table1.rtf'
 STYLE = MINIMAL /* Basic MS Word table, no color */
 BODYTITLE;

 SAS Procedure(s)

ODS RTF CLOSE;
     Be very careful of ODS BODYTITLE option!               35
           Customize RTF File:
      Header/Footer Sections - Titles

 Place font attributes before the text to apply

 Syntax is different from footnote and SAS Procedures:
  PRINT, REPORT, and TABULATE

 In version 8.2, Page X of Y feature requires print
  review/print to take affect.
  The following syntax is required:
  "{Page} {\field{\*\fldinst{ PAGE }}} \~{of}\~{\field{\*\fldinst {
  NUMPAGES }}}"


                                                                      36
       Example 6. 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> Face
                <bold|median|light>             Weight
                <italic|roman|slant>;           Style
   Height: height=8pt;
   Justify: j=<left|right|center>;
   Superscript: ^{super &text};       Subscript: ^{sub &text};
   Page X of Y: ^{pageof}; (V 9.0)                        37
         Customize RTF File:
  Header/Footer Sections - Footnotes


 Place font attributes before the text to apply

 Syntax is same as SAS Procedures: PRINT, REPORT,
  and TABULATE

 Remember to turn off style just like like turning off ODS




                                                          38
 Example 6. Customize RTF File:
   Header/Footer Sections -
          Footnotes
Footnote1 ”^S={font_face=arial font_style=italic}
  ^{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={}”;   Face
   “^S={font_weight=bold}&text ^S={}”;     Weight
   “^S={font_style=italic}&text ^S={}”;    Style
                                                    39
          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;



                                                                40
     Example 6. Customize RTF File:
        Using PROC TABULATE
%let mystyle =%str(font_face="arial" font_weight=bold);
(PROC FORMAT; value gender, race, pctpct; quit;)

PROC TABULATE data=demog missing formchar=' -----------'
style=[font_face=" courier"];

class gender race drug/style=[&mystyle];
format gender gender. race race.;
table (gender race), drug=' '*
     (n='N'*f=7. pctn<gender race>=' %' *f=pctpct. )
     / box=[label='Baseline '
       style=[&mystyle] ] rts=43;
run;                                                   41
Example 6. Summary Table : Output
     Using PROC TABULATE       RTF




   Arial,
   Bold
                           Courier




                                     42
     Example 7. Customize RTF File:
        Using PROC TABULATE
OPTIONS ORIENTATION=LANDSCAPE nodate center;

%let mystyle =%str(font_face="arial" font_weight=bold);
PROC TABULATE data=demog missing formchar=' -----------'
style=[font_face=" courier"];

class gender race drug/style=[&mystyle];
classlev gender race drug/style=[&mystyle];
format gender gender. race race.;
keyword n pctn/style=[&mystyle];
table (gender race), drug=' '*
     (n='N'*f=7. pctn<gender race>=' %' *f=pctpct. )
     / box=[label='Baseline '
       style=[&mystyle] ] rts=43;                      43
run;
Example 7. Summary Table : Output
   Using PROC TABULATE w/
                                 RTF
    CLASSLEV and KEYWORD


                             Arial,
                             Bold



 Arial,
 Bold




                                      44
               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;
                                                            45
    Example 8a. Customize RTF File:
         Using PROC PRINT
ODS RTF FILE = 'C:\how\gupta\table3.rtf' STYLE = MINIMAL
 BODYTITLE;

PROC PRINT data=demog (obs=5) noobs
 style(header) = [font_face="arial" font_weight=bold]
 style(data)= [font_face="arial"];
 var patient drug gender race age height weight;
 fomat gender gender. race race.;
run;

ODS RTF Close;
                                                        46
Example 8a. Customize RTF File:   RTF
     Using PROC PRINT

 Arial, Bold




 Arial




                                   47
          Example 8b. Table with 2
                 Columns
ODS RTF FILE = 'C:\how\gupta\table3.rtf' STYLE = MINIMAL
 COLUMNS=2;

PROC PRINT data=demog
 style(header) = [font_face="arial" font_weight=bold]
 style(data)= [font_face="arial"];
 var patient drug gender race age height weight;
 fomat gender gender. race race.;
run;

ODS RTF Close;
                                                        48
Example 8b. Table with 2   RTF
       Columns




                           49
   Summary: Where to Go From
             Here

 Programmer, Statistician, Manager
 Power & Flexibility
 Customizing Output
 Creating Excel Files
 RTF-control words
 Applying Style Syntax in Proc Tabulate and Proc
  Print

                                                50
       SAS® ODS Technology for
        Today’s Decision Makers
ODS RESOURCES
 The Complete Guide to the SAS Output Delivery System
  Version 8

 http://www.sas.com/rnd/base/index-ods-resources.html

 Quick Results with the Output Delivery System

 Output Delivery System: The Basics

 Chevell Parker, Generating Custom Excel Spreadsheets
  Using ODS
                                                         51
SAS® ODS Technology for
 Today’s Decision Makers




  Sunil.Gupta@Quintiles.com
          Quintiles
                              52
SAS® ODS Technology
 for Today’s Decision
       Makers


      Sunil Gupta
        Quintiles

                        53

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:8
posted:12/30/2010
language:English
pages:53