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