automation of summary displays - daa.com.au · pdf fileystats_sarahbruce_presentation author:...
TRANSCRIPT
STRATEGIC
INFORMATION
CONSULTANTS
Sarah Bruce, Emma Smith, Luke Mullins
Data Analysis Australia
February 2015
Automation of Summary Displays
STRATEGIC
INFORMATION
CONSULTANTS
Introduction• Summary displays
• Summary tables, plots, images, etc
• Formatted summary tables
• Automation• Automation for the programmer/consultant
• Writing code to produce summary displays that are:
• Quickly and simply repeated
• Easily altered
• Can be produced repeatedly with minimal effort
• The opposite of manually producing summary outputs
Page 1
STRATEGIC
INFORMATION
CONSULTANTS Page 2
• Black box• No or very few inputs
• Mostly assumptions
• Eg. “Enter <name>” → *click* → “<name>, this is your life expectancy”
• The Trade Off• Assumptions
• Inputs
• Time
The Black Box Trade Off
STRATEGIC
INFORMATION
CONSULTANTS
Assumptions and Inputs• Making educated guesses about what you expect to change (or
not change) in the future
• More assumptions• Less inputs
• Less time to produce outputs (assuming code is written efficiently)
• More like a black box
• More difficult/time consuming to make changes
• Less assumptions• More inputs and more time
• Takes time to enter inputs when producing summary displays
• Too many inputs can become confusing
• e.g.. If each run requires 25 inputs to be changed user error becomes more likely
• Less automated
Page 3
STRATEGIC
INFORMATION
CONSULTANTS
Time• It takes time to automate
• Generally the more automated, the more assumptions there are and the longer it takes to write
• Saves time in the long run• Takes time now but saves time later
• Balance between how much time is spent automating something and how much time it saves in the future
Page 4
STRATEGIC
INFORMATION
CONSULTANTS
Pros & Cons of Automation
+ Summary outputs are rarely produced only once
• Revisions or changes to input data or output requirements
• Project involves comparisons
+ Reduces likelihood of human error
+ By automating you’re planning for the “worst”
- Takes time and often don’t have the time when
it’s needed
- Far more time consuming if assumptions change
Page 5
STRATEGIC
INFORMATION
CONSULTANTS
Pros & Cons of AutomationFormatting Summary Displays
+ Quickly changes outputs from R/SAS into “client friendly” outputs for delivery
• Client friendly outputs are more meaningful to the client and reduces any ambiguity in results
• Example – formatting CSV outputs using VBA in Excel
+ Keeps formatted outputs consistent
+ Allows small changes to the format to be done with
relative ease
- Is more time consuming if not familiar with VBA
Page 6
STRATEGIC
INFORMATION
CONSULTANTS
Tips in R/SAS• Think about your inputs
• Eg. 2015 → 2014/2015 → 14/15 → 2014/15....
• Need only input 2015 and can derive the others
• Inputs are best placed at the top of a code• Allows for easier changes to inputs as they are easy to locate.
• Helps reduce the likelihood of user error
• Modularise your code• Makes finding assumptions easier if it’s necessary to change an
assumption
• Use loops – “2 or more do a for”• If a particular part of a script repeats 2 or more times it’s wise to create a
“for” loop
Page 7
STRATEGIC
INFORMATION
CONSULTANTS
Tips in VBA• Two options
• Record Macro
• Write own Macro
• Record Macro creates a black box
• Writing own Macro• Make a template using output from R/SAS
• Avoid direct cell references
• Use formulas such as Index and match
• Can specify the inputs
Page 8
STRATEGIC
INFORMATION
CONSULTANTS
Automation Example
Page 9
STRATEGIC
INFORMATION
CONSULTANTS
Automation Example
Page 10
STRATEGIC
INFORMATION
CONSULTANTS
Automation Example
Page 11
• Run Macro• A sheet for inputs and
to start macro.
• Template• What you want your
output to look like.
• smry Output• Where the data gets
copied into
STRATEGIC
INFORMATION
CONSULTANTS
Automation Example
Page 12
=IF(Column heading = "", "", INDEX('smry Output‘ARRAY, MATCH(Row Heading, 'smry Output‘Row Headings,0), MATCH(Column Heading,'smry Output‘Column Headings,0)))