Transcript Delivering Multi-Sheet Excel Reports from a Parameterized

Delivering
Multi-Sheet Excel Reports
from a
Parameterized Stored Process
TU02
Richard DeVenezia, Independent Consultant
Harry Droogendyk, Stratia Consulting Inc.
Stored Process
• SAS code
• made available to wider audience
– registered in the Metadata
– accessible via different interfaces
• single version of the truth
• can be data processes
• create output, in this case ODS
Stored Process
• defined via Enterprise Guide
– GUI interface
– allows incremental development
– run, review, modify
– happy?
• register Stored Process in Metadata using EG
wizard
• executed from EG, MS Add-in, Browser
The Data
• find it in Appendix A in the paper
• series of single answer questions relating
to Travel satisfaction
– randomly generated
• demographic information
– gender, income level, city, country
• code reshapes data for ease of use
Define Library
• SAS Management Console
– define library, import tables
Define Library
• verify in EG Explorer ( under Tools )
Define List Process
• under File, select New / Project
• from the Task List toolbar, drag Sort Data
to the grid
Define List Process
• must first select a data source
• select SAS Servers, SASMain, Libraries
and the Survey libname and the RESULTS
dataset
Define List Process
• the SORT wizard appears, drag, Run
Define List Process
• completed SORT displays temporary data
set
Define List Process
• after closing the data set, drag List Data to
grid
Define List Process
• list wizard appears, drag, Run
Define List Process
Define Tabulate Process
• TABULATE procedure uses a different
data source
• EG tries to anticipate our needs
– wants to use output of previous step
– saw this in the LIST process
• must force EG to use a new data set
Define Tabulate Process
• Server List in
Task List
toolbar
• drag dataset
VECTOR3 to
grid
Define Tabulate Process
• drag Summary
Tables to grid
• report will be
connected to the
VECTOR3 data
set
Define Tabulate Process
• drag fields to Summary Roles
Define Tabulate Process
• drag fields to Summary Tables
Define Tabulate Process
• drag fields to Summary Tables
Define Tabulate Process
• after Preview Code, click Insert Code
Define Tabulate Process
• have to pay attention !!!
Define Tabulate Process
• double-click and a small window opens
Define Tabulate Process
• scroll code window
Define Tabulate Process
• report appears
Define Pie Chart
• pie chart creation requires subsetting
• click the VECTOR3 data set to ensure it's
active
• from the menu bar, Data, Filter and Query
• wizard appears
Define Pie Chart
Define Pie Chart
• drag fields into the Select area
• click Filter Data tab, New Filter icon
Define Pie Chart
• define Filter criteria
Define Pie Chart
• define Sort criteria and click Run
Define Pie Chart
• define Pie Chart properties
Define Pie Chart
Creating the Stored Process
• creating a parameterized Stored Process
was our goal
– ability for end user to select what's displayed
• use the initial "listing" report we created
– Open ( double click ) the List icon
– Preview Code
– Insert Code
– add WHERE clause to PROC PRINT
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Creating the Stored Process
Executing a Stored Process
Executing a Stored Process
Executing a Stored Process
Executing a Stored Process
Executing a Stored Process
Executing a Stored Process
Executing a Stored Process
Executing a Stored Process
Multi-Sheet Output
• ODS ExcelXP tagsets do create multisheet Excel output
• requires Excel 2002 ( XP ) or 2003
• unfortunately…. streaming output not
supported in v9.1.3
• EG doesn't play well with XML either
• workaround available
Multi-Sheet Output
Multi-Sheet Output
Multi-Sheet Output
Conclusion
• EG provides an effective means of:
• defining SAS processes
• turning those processes into Stored
Processes
• defining parameters for the STP
• streamed output does not support XML by
default
• must define MIME type appropriately
• what's in store for v9.2 ??
Thank you !
Richard A DeVenezia
www.devenezia.com
Harry Droogendyk
[email protected]
Acknowledgements:
Rupinder Dhillion, Peter Eberhardt and
Vince DelGobbo