Delivering Multi-Sheet Excel Reports from a Parameterized Stored Process

1
Delivering Multi-Sheet Excel Reports from a
Parameterized Stored Process

• TU02
• Richard DeVenezia, Independent Consultant
• Harry Droogendyk, Stratia Consulting Inc.

2
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

3
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

4
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

5
Define Library

• SAS Management Console
• define library, import tables

6
Define Library

• verify in EG Explorer ( under Tools )

7
Define List Process

• under File, select New / Project
• from the Task List toolbar, drag Sort Data to the
  grid

8
Define List Process

• must first select a data source
• select SAS Servers, SASMain, Libraries and the
  Survey libname and the RESULTS dataset

9
Define List Process

• the SORT wizard appears, drag, Run

10
Define List Process

• completed SORT displays temporary data set

11
Define List Process

• after closing the data set, drag List Data to
  grid

12
Define List Process

• list wizard appears, drag, Run

13
Define List Process
14
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

15
Define Tabulate Process

• Server List in Task List toolbar
• drag dataset VECTOR3 to grid

16
Define Tabulate Process

• drag Summary Tables to grid
• report will be connected to the VECTOR3 data set

17
Define Tabulate Process

• drag fields to Summary Roles

18
Define Tabulate Process

• drag fields to Summary Tables

19
Define Tabulate Process

• drag fields to Summary Tables

20
Define Tabulate Process

• after Preview Code, click Insert Code

21
Define Tabulate Process

• have to pay attention !!!

22
Define Tabulate Process

• double-click and a small window opens

23
Define Tabulate Process

• scroll code window

24
Define Tabulate Process

• report appears

25
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

26
Define Pie Chart
27
Define Pie Chart

• drag fields into the Select area
• click Filter Data tab, New Filter icon

28
Define Pie Chart

• define Filter criteria

29
Define Pie Chart

• define Sort criteria and click Run

30
Define Pie Chart

• define Pie Chart properties

31
Define Pie Chart
32
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

33
Creating the Stored Process
34
Creating the Stored Process
35
Creating the Stored Process
36
Creating the Stored Process
37
Creating the Stored Process
38
Creating the Stored Process
39
Creating the Stored Process
40
Creating the Stored Process
41
Creating the Stored Process
42
Creating the Stored Process
43
Creating the Stored Process
44
Creating the Stored Process
45
Creating the Stored Process
46
Creating the Stored Process
47
Creating the Stored Process
48
Creating the Stored Process
49
Creating the Stored Process
50
Creating the Stored Process
51
Executing a Stored Process
52
Executing a Stored Process
53
Executing a Stored Process
54
Executing a Stored Process
55
Executing a Stored Process
56
Executing a Stored Process
57
Executing a Stored Process
58
Executing a Stored Process
59
Multi-Sheet Output

• ODS ExcelXP tagsets do create multi-sheet 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

60
Multi-Sheet Output
61
Multi-Sheet Output
62
Multi-Sheet Output
63
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 ??

64
Thank you !
Richard A DeVenezia www.devenezia.com Harry
Droogendyk conf_at_stratia.ca Acknowledgements Rupi
nder Dhillion, Peter Eberhardt and Vince DelGobbo