excel’ing at : june 19, 2003 ruth sanderson, epidemiologist
Post on 19-Dec-2015
220 views
TRANSCRIPT
EXCEL’ing at :
June 19, 2003 Ruth Sanderson, Epidemiologist
OverviewWhy Innovate?
RRFSS analysis requirements and analysis tools
Overview of AutoCiOutput, Description
Demonstration of AutoCITricks
Conclusions
Why Innovate?
Needed faster way to comply with Analysis Guidelines:
• Denominator cell size<30 not to be released• Numerator cell size<5 not to be released• Coefficient of Variation >33.3 not to be
released (16.6-33.3 interpret with caution)• Estimates include 95% confidence intervals
Why Innovate?
Wanted:
Excel graphs with error bars that can be pasted into a Word document for a clean report
Walking Trail Use and Knowledge in past Year by Residence, Middlesex-London Health Unit, 2001-2002
0
10
20
30
40
50
60
70
80
Pe
rce
nt
(%)
Middlesex County 40.6 35.2 24.2
London 58.9 25.7 15.4
Used Know and Not Used Do Not Know
Source: RRFSS , Waves 5-20
Current Analysis Tools
• SPSS Syntax files or crosstab to get unweighted/ weighted estimates– Does not provide CV, CI or flag small cells
• Confidence Interval Syntax File from P. Holowaty, 1999– Requires transcription of numbers- prone to
human error & labour intensive
AutoCI - Output Overview
Outputs: 3 Components in Excel Spreadsheet:• Unweighted Table• Weighted Table
– Includes CI, CV and Warnings in Colour
• Excel Bar Graph with Error Bars – Basis for customized Graphs
AutoCI Overview- OutputUNWEIGHTED VALUES WEIGHTED VALUES
household income40 Total household income40< $40,00040 to < 70,00070 to < 100,000$100,000+ < $40,00040 to < 70,00070 to < 100,000$100,000+
Count 61 42 19 23 145 Yes Count 52 44 22 27% within household income4075.3 85.7 95 92 82.9 % within household income4076.5 88 95.7 93.1
+-95% C.I. 10.1 9 8.3 9.2C.V. 6.7 5.2 4.4 5.1
Count 20 7 1 2 30 No Count 16 6 1 2% within household income4024.7 14.3 5 8 17.1 % within household income4023.5 12 4.3 6.9
+-95% C.I. 10.1 9 8.3 9.2C.V. 21.9 38.3 98.4 68.2
Count 81 49 20 25 175 Total Count 68 50 23 29% within household income40100 100 100 100 100 % within household income40100 100 100 100
Many restaurants have been given an "Award of Excellence", a special designation for exceptional food safety & food handling, .... Are you aware of any restaurants in your community that have been given this award? * household income40 CrosstabulationMany restaurants have been given an "Award of Excellence", a special designation for exceptional food safety & food handling, .... Are you aware of any restaurants in your community that have been given this award? * household income40 Crosstabulation household income40 Total household income40
< $40,000 40 to < 70,00070 to < 100,000$100,000+ < $40,000 40 to < 70,00070 to < 100,000$100,000+
[London o
[London only] In the past year, have you eaten at OR ordered take-out food from
arestaurant, icluding family style restaurants as well as cafeteria style and fast
food restaurants?
0
20
40
60
80
100
120
%P
erc
en
t
Yes
No
Yes 76.5 88 95.7 93.1
No 23.5 12 4.3 6.9
< $40,000 40 to < 70,000 70 to < 100,000 $100,000+
Many restaurants have been given an "Award of Excellence", a special designation for exceptional food safety & food handling, .... Are you aware of any restaurants
in your community that have been given this award?
0
50
100
150
Yes
No
don't know
Yes 13.4 18 8.7 17.2
No 83.6 82 91.3 75.9
don't know 3 0 0 6.9
< $40,00040 to < 70,000
70 to < 100,000
$100,000+
AutoCI Output WarningsWEIGHTED VALUES
household income40 Total< $40,00040 to < 70,00070 to < 100,000$100,000+
Yes Count 52 44 22 27 145% within household income4076.5 88 95.7 93.1 85.3+-95% C.I. 10.1 9 8.3 9.2 5.3C.V. 6.7 5.2 4.4 5.1 3.2
No Count 16 6 1 2 25% within household income4023.5 12 4.3 6.9 14.7+-95% C.I. 10.1 9 8.3 9.2 5.3C.V. 21.9 38.3 98.4 68.2 18.5
Total Count 68 50 23 29 170% within household income40100 100 100 100 100
Many restaurants have been given an "Award of Excellence", a special designation for exceptional food safety & food handling, .... Are you aware of any restaurants in your community that have been given this award? * household income40 Crosstabulation household income40 Total
$100,000+ < $40,000 40 to < 70,000 70 to < 100,000 $100,000+Many restaurants have been given an "Award of Excellence", a special designation for exceptional food safety & food handling, .... Are you aware of any restaurants in your community that have been given this award?Yes Count 9 9 2 5 25
% within household income40 13.4 18 8.7 17.2 14.8+-95% C.I. 8.2 10.6 11.5 13.7 5.4C.V. 31.1 30.2 67.5 40.7 18.5
No Count 56 41 21 22 140% within household income40 83.6 82 91.3 75.9 82.8+-95% C.I. 8.9 10.6 11.5 15.6 5.7
[London only] In the past year, have you eaten at
OR ordered take-out food from arestaurant,
icluding family style restaurants as well as cafeteria style and fast
food restaurants?
Many restaurants have been given an "Award of Excellence", a special designation for exceptional food safety & food handling, .... Are you aware of any restaurants in your community that have been given this award?
0
50
100
150
%P
erc
en
t
Yes
No
don't know
Yes 13.4 18 8.7 17.2
No 83.6 82 91.3 75.9
don't know 3 0 0 6.9
< $40,000 40 to < 70,000 70 to < 100,000 $100,000+
Linked back to unweighted table
Output Graph & Error Bars[London only] In the past year, have you eaten at OR ordered take-out food from arestaurant,
icluding family style restaurants as well as cafeteria style and fast food restaurants?
0
20
40
60
80
100
120
YesNo
Yes 76.5 88 95.7 93.1
No 23.5 12 4.3 6.9
< $40,000 40 to < 70,000 70 to < 100,000 $100,000+
Auto CI- How does it Work?
• Run SPSS Syntax File calls on a script file• Script File:
– instructs SPSS to copy and past the unweighted and weighted output tables into Excel
– instructs macro in Excel to run and return to SPSS
• Excel macro calculates CI’s, CV’s, inserts warning colours & creates graph/ error bars
Auto CI- What do you need to make it Work?
• Software: SPSS, Excel• SPSS file title: test.sps• Excel file titled: autoci.xls• Files by B. Lueske, MLHU
• must have already established/created variable of interest & proper weight variable
Demo
Conclusions• AutoCi builds on and is dependent on the work of
other RRFSS partners– syntax files, CI calculator, weighting calculations,
analysis requirements
• AutoCI automates analysis requirements– calculating CV’s, CI, small cell count identification
• AutoCi provides quick visual of differences in estimates– Excel bar graph with error bars that can be modified or
pasted into Word documents