Data from several datasets joined and transformed. • Multiple (9) output datasets created from these joined datasets. • Output data loaded into Excel...
Turn-Key Performance Metrics using Base SAS® and Excel VBA. Michael C. Frick, Warren, MI. ABSTRACT. In today's competitive environment, everyone is
ABSTRACT. There have been many excellent papers detailing the transfer of data from SAS® to Microsoft Excel® using various methods such as DDE, ODS
Text!Analytics!using!High!Performance!SAS ... The!outputfrom!text mining process ... /UserManual/CRISPPDM.pdf!! Fayyad,!U.M.!et.al.!
C – programming E.Balagurusamy Tata McGray Hill. 4. How to solve it by ... with C : Gottfried. 6. The C programming language : Kerninghan and Ritchie. 7. Programming in ANSI C : Ramkumar Agarwal. 8. Mastering C by Venugopal, Prasad – TMH. 9. Let Us C
1 Paper 135-30 Creating and Using SAS® Stored Processes with SAS® Enterprise Guide® Eric Rossland, SAS Institute Inc., Dallas, TX Kari Richardson, SAS Institute
A SunCam online continuing education course. Programming MS Excel in Visual Basic (VBA). Part 4-Error Handling, Arrays, Dialog Boxes, Charts by. Kwabena Ofosu, Ph.D., P.E., PTOE
MsgBox msg, vbInformation, “Dedication”. End Sub. Author's Acknowledgments. I' m grateful to all the talented people at Wiley Publishing for giving me the opportunity to write Excel books. Special thanks to Kelly Ewing (project editor) and Niek Otten
1 Paper 243 -29 SASâ Macro Programming for Beginners Susan J. Slaughter, Avocet Solutions, Davis, CA Lora D. Delwiche, Delwiche Consulting, Winters, CA
Excel VBA Programming: Functions. (Dr. Tom Co, 9/14/2008). Introductory items: 1. A function a group of statements (or code) that yields a value. 2. There are
Jul 17, 2012 ... for Applications (VBA) in conjunction with the Excel spreadsheet, the user has the convenience ... But VBA, which has been available since Excel 5.0 and is a subset of Visual Basictm, is a full- featured ...... Ribando, R.J., "An Exc
1.11.2 Input Operator. 1.11.3 Cascading I/O Operator. 1.12 Example with Class. 1.13 Structure of C++. 1.14 Creating Source File. 1.15 Compiling and Linking ..... Namespace is a new concept introduced by the ANSI C++ standards committee. This defines
This is a PDF version of a free on-line book that is available at ...... ming for the first time, and it is as much about general programming concepts as it is about. Java in ... of Java's more advanced capabilities. ∗ ∗ ∗. The Seventh Edition of “ I
The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2001. Step-by-Step Programming with Base SAS® Software. Cary, NC: SAS Institute Inc
2005 by Thomson Course Technology PTR. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or
Introduction to Programming Using Java. Version 7.0, August 2014. (Version 7.0. 2, with just a few corrections, December 2016). David J. Eck. Hobart and William Smith Colleges. This is a PDF version of a free, on-line book that is available at http:/
This is a PDF version of a free on-line book that is available at ...... of Java's more advanced capabilities. ∗ ∗ ∗. The Seventh Edition of “Introduction to Programming using Java” is not a huge update from the sixth edition. In fact, my main motiva
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the. Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of J
http://www.columbia.edu/~gz2165/excel.html. Lesson materials: Learning Slides[.pdf]. Exercises - Blank [.xlsx]. Exercises - Filled [.xlsm]. Created by
This is a PDF version of an on-line book that is available at ... 1.3 The Java Virtual Machine ... 4.6 More on Program Design
Applications (VBA), the macro programming language that is included with: ... VBA is probably the most complex feature in Excel, and it's easy to get over-
Department of Mathematics. University of Connecticut. Storrs, CT, USA. An Introduction to Excel VBA. Programming: with Applications in Finance and Insurance
2.2 Rules for collapse of dual lines (road casings) to centerlines The task of creating road centerlines from dual-line casings is an important generalization
Great Graphics Using Proc Sgplot, Proc Sgscatter, and ODS Graphics for SAS®/Stat Procedures Kathy Welch CSCAR The University of Michigan
Automating SAS AMO using VBA Programming Scott Bass Senior Information Analyst Bupa Australia
AMO: An Opportunity for Transformation •
Business Case for Using AMO
•
Old Approach
•
New Approach
•
DEMO
•
Wrap Up
2
Business Case for Using AMO •
Data from several datasets joined and transformed
•
Multiple (9) output datasets created from these joined datasets
•
Output data loaded into Excel
•
Extensive editing done in Excel (“hospital hmodelling”)
◦ This editing is more an “art” than “science” and can’t be done programmatically (“Rubik’s Cube”) •
Final results are sent to external organisations for review ◦ They don’t have SAS
•
Conclusion: Excel is the proper tool for this job
•
This process will be repeated for ~ 200 organisations over the next 18 months (as of 2013 Q1)
3
Old Approach •
Enterprise Guide (EG) project was used to join the datasets
•
Data was filtered in EG by the external organisation ID, start date, and end date
•
Filtering criteria was hard coded into the SQL queries o User knew SQL but unaware of EG prompting
•
Multiple (9) CSV files created from the output datasets
•
VBA macro used to import the CSV files into multiple worksheets
•
CSV import problematic due to lack of data typing in CSV files
Issues: 1. EG project had to be re-edited and re-run every week or so to apply the filtering 2. Lots of manual editing to fix data incorrectly changed by Excel 3. 1 – 1.5 days spent in getting the data prepared in Excel before other work can begin 4
New Approach •
(New) EG project still used to join the datasets
•
No filtering is done in EG, so the output datasets contain all organisations and dates
•
SAS output datasets created instead of CSV files (SPDE engine)
•
SAS AMO used to import the data into Excel directly from the server
•
VBA programming used to set the filtering criteria from within Excel
•
EG project only needs to be run every couple months as new claims data arrives
Results: 1. Data is correctly imported by AMO as it “knows” the data type 2. It’s a much easier process to initialise data for a new model (just click a button) 3. Process reduced from 1.5 days to 5 minutes to initiate a new model
5
How did we achieve this Transformation?
DEMO...
6
Book1 – Key Points: •
Open SAS dataset into Excel
•
Demonstrate key AMO features o SAS Data o Tasks o Reports (Stored Processes) o Quick Start o SAS Favorites o Modify o Properties o Manage Content o Tools o Per View/View All
o Begin Edit/Commit o Filter and Sort o Name Manager (Formulas Tab) 7
Book1 – SAS Menu Tab
8
Book1 – SAS Data
9
Book1 – SAS Data (cont)
10
Book1 – SAS Data (cont)
11
Book1 – Tasks
12
Book1 – Tasks (cont)
13
Book1 – Tasks (cont)
14
Book1 – Tasks (cont)
15
Book1 – Tasks (cont)
16
Book1 – Tasks (cont)
17
Book1 – Tasks (cont)
18
Book1 – Reports
19
Book1 – Reports (cont)
20
Book1 – Quick Start
21
Book1 – Quick Start – Task Gallery
22
Book1 – Quick Start – Automatic Chart
23
Book1 – Quick Start – Automatic Chart (cont)
24
Book1 – Favorites
25
Book1 – Properties
26
Book1 – Properties (cont)
27
Book1 – Properties (cont)
28
Book1 – Properties (cont)
29
Book1 – Manage Content
30
Book1 – Tools
31
Book1 – Per View/View All
32
Book1 – Edit Mode
33
Book1 – Commit
34
Book1 – Filter & Sort
35
Book1 – Filter & Sort (cont)
36
Book1 – Filter & Sort (cont)
37
Book1 – Name Manager
38
Book2 – Key Points: •
Simple VBA programming to change object properties (filter) and execute methods (refresh)
•
Add reference to SAS AMO plugin in VBA project (Tools References)
•
Insert module to contain VBA code (Insert Module)
•
Show Object Browser
•
Create SAS Data filter string using VBA
•
Refresh SAS Data object using VBA
•
Create and Use Named Ranges in VBA programs for easier maintenance
39
Book2 – Add Reference to SAS AMO Plugin Visual Basic Editor (Alt-F11) Tools References:
Can be interesting to see the Properties and Methods of available Classes
41
Book2 – Named Ranges Create and Use Named Ranges for easier and more maintainable coding
42
Book3 – Key Points: •
Using more generic VBA code to update all SAS data objects
•
Update multiple SAS data objects specifying a single filter in Excel
•
Demonstrate that SAS objects are deleted when a worksheet is deleted
43
Book4 – Key Points: •
Using a Named Range for Input Prompts
•
Demonstrating event processing each time a prompt value is changed
44
Book5 – Key Points: •
Adding a button to a worksheet
•
Binding a macro to that button
•
Refreshing SAS content using that button
45
Book6 – Key Points: •
Using Stored Processes (called "Reports") in Excel
•
Binding an Input Stream to a Named Range
•
Display the XML stream sent to SAS
46
Book7 – Key Points: •
Demonstrate a generic approach to passing name/value pairs (macro variables) from Excel to a Stored Process
•
Use those name/value pairs to filter the output dataset
•
Use the Style Manager to format the output
•
No VBA code used, just built-in AMO functionality and the Refresh button
47
Book8 – Key Points: •
Binding Stored Process input and output prompts to Excel cells
48
Book9 – Key Points: •
Using VBA to refresh Stored Process
•
Show the online help
49
Book9 – Online Help
50
Summary •
If the “landing zone” for your data is Excel, think AMO
•
Data can be communicated between Excel and the remote SAS Session
•
VBA is available to “drive” SAS AMO
•
You don’t need to be an expert VBA programmer
51
References Tips and Techniques for Automating the SAS® Add-In for Microsoft Office with Visual Basic for Applications Tim Beese, SAS Institute Inc., Cary, NC http://support.sas.com/resources/papers/proceedings11/012-2011.pdf
Excelling with Excel Tim Beese and Greg Granger, SAS Institute Inc., Cary, NC http://support.sas.com/resources/papers/proceedings12/036-2012.pdf
Help for the SAS Add-In for Microsoft Office (Accessible from the SAS AMO Help button) See especially “Automating the SAS Add-In with Visual Basic Code” chapter
