Friday Half-Day Class Kirk Lafler

Custom Reporting with PROC REPORT and ODS Excel (Hands-On)

Presented: Friday September 7, 2018, 1:30pm-5:00pm

Presented by:
Kirk Paul Lafler is an entrepreneur, consultant, and founder at Software Intelligence Corporation, and has been using SAS since 1979. Kirk is a SAS application developer, programmer, Certified Professional, provider of SAS consulting and training services, mentor, advisor and adjunct professor at University of California San Diego Extension, emeritus Advisory Board member, and educator to SAS users around the world. As the author of six books including Google® Search Complete (Odyssey Press, 2014) and PROC SQL: Beyond the Basics Using SAS, Second Edition (SAS Press, 2013); Kirk has written hundreds of papers and articles; served as an invited speaker, trainer, keynote and section leader at international, regional, special-interest, local, and in-house SAS user group conferences and meetings; and is the recipient of 25 Best Contributed awards.

SAS users turn to the REPORT procedure to customize and satisfy all their reporting needs for the creation and delivery of quality “custom” detail and summary reports, and specialized output for management, end users, and customers. This new and popular course teaches SAS users an assortment of techniques to create custom reports and specialized output using PROC REPORT and the powerful ODS Excel destination. Attendees learn how to create detail and summary reports using PROC REPORT; acquire useful Output Delivery System (ODS) skills; combine PROC REPORT and the powerful ODS Excel destination to produce quick and formatted detail and summary Excel workbook results; customize output and results with SAS-supplied styles; compute subtotals and totals at the end of a report using a COMPUTE Block; calculate percentages; produce statistics for analysis variables; apply conditional logic to control summary output rows; add background images; build custom autofilter drill-down (interactive) reports and Excel workbooks; and add traffic lighting scenarios to Excel workbooks.

Intended Audience: Beginner to intermediate

Tools Discussed: Base SAS, Excel

Prerequisite: None

Class Outline:

  • PROC REPORT Essentials, Compute Blocks and Logic
    • Find Distinct Variable Levels (By-groups)
    • Types of Reports Produced by PROC REPORT
    • Steps in Designing a Report with PROC REPORT
    • PROC REPORT General Syntax
      • PROC REPORT Options
      • PROC REPORT Statements
      • PROC REPORT Statement Options
    • Logic in Compute Blocks
    • PROC REPORT Examples
  • Output Delivery System (ODS) Excel Destination Basics
    • SAS-ODS Transfer Process
    • ODS Destinations
    • Defining an Output Object
      • The Data Component
      • The Template Component
    • Identifying Output Objects with ODS TRACE
    • Output Objects and Selection (or Exclusion) Lists
      • Selecting a Single Output Object
      • Selecting Multiple Output Objects
    • Facts About ODS Destinations
    • Available Transfer Methods to Excel
    • The ODS Excel Destination
      • What Can be Done with ODS Excel
      • ODS Excel Syntax
      • Listing Available Style Templates
      • ODS Excel Examples
  • Customizing Output with ODS Excel Options
    • ODS Excel Options and SubOptions for Customizing Output
      • List of SubOptions and Descriptions
    • Creating a New Sheet for Each By-group
    • Customizing Output with ODS Excel Examples
  • Adding Background Images and Autofilters to Excel Workbooks
    • Background Images
      • Background Image Features
      • Adding a Background Image
      • Centering a Background Image
    • Creating an Autofilter Excel Report
      • What is an Autofilter?
      • Creating an Autofilter in an Excel Report
      • Creating a Hybrid Autofilter in an Excel Report
    • Producing Multiple Excel Files
  • Adding Traffic Lighting to Excel Workbooks
    • Traffic Lighting Features
    • Column Traffic Lighting – Foreground Text
    • Column Traffic Lighting – Background
    • Custom Traffic Lighting – Columns
    • Custom Traffic Lighting – Rows