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