Select Page

Farrokh Alemi, PhD
Analysis of Observed & Expected
Infections
Farrokh Alemi, Ph.D.
Copy Data into Excel
Week
1.00
2.00
3.00
4.00
5.00
6.00
0.90
0.70
0.80
0.50
0.30
0.30
Number
Risk of Infection for each Patient
Infected
0.80
0.70
0.80
0.90
0.85
6.00
0.80
0.70
0.60
0.80
0.90
5.00
0.95
0.92
0.87
4.00
0.60
0.66
0.67
3.00
0.40
0.50
0.40
0.50
0.34
2.00
0.40
0.50
1.00
Calculated Expected Rate
Expected
Infection
0.83
0.75
0.89
0.61
0.41
0.40
=AVERAGE(expected rate for each patient)
Calculate Expected Deviation
0.15 = SQRT(SUMPRODUCT(B2:G2,B9:G9))/COUNTA(B2:G2)
Look Up t-Values
Number Expected
Infected Infection
6.00
0.00
5.00
0.75
4.00
0.89
3.00
0.61
2.00
0.41
1.00
0.40
Expected One sided
Deviation
t value
0.15
2.015
0.17
2.015
0.16
2.353
0.24
2.353
0.20
2.015
0.28
2.920
Degrees of freedom is
one minus number of
observations
1-sided assumes
observations can only
exceed upper limit
Calculate Upper and Lower Limits
Number Expected Expected
t value
Infected Infection Deviation
6.00
5.00
4.00
3.00
2.00
1.00
0.00
0.75
0.89
0.61
0.41
0.40
0.15
0.17
0.16
0.24
0.20
0.28
2.015
2.015
2.353
2.353
2.015
2.92
Upper
Limit
Lower
Limit
Observed
1.00
1.00
1.00
1.00
0.81
1.00
0.52
0.40
0.52
0.04
0.01
0.00
1.00
0.83
1.00
0.75
0.33
0.33
Limits above 1 set to 1, limits below 0 set to 0
Plot the Control Chart
Has Rate of Hospitals Exceeding
National Payment for AMI Changed?
Farrokh Alemi, Ph.D.
2. Unzip
3. Rename
To H-YYYY-MM
Merge into Access
in Order of Date
1
2
4
3
Note Change in File Names
From 2015 01 database
From 2015 04
From 2015 05
From 2015 07 database
From 2015 10
From 2015 12
From 2016 05
From 2016 08
From 2016 11 database
Only 3 Years
Show Change
in Data
Ignore Tables with Same Time Periods
Only 3 unique time periods in 9 tables:
• 2010-2013 in HQI_HOSP_AMI_Payment
• 2011-2014 in HQI_HOSP_Payment
• 2012-2015 in HQI_HOSP_PaymentAndValueOfCare
Field Names Change in Different Files
Prob2010-2013:
Sum(InStr([HQI_HOSP_AMI_Payment]![Compared to National],”Greater”))/
Count([HQI_HOSP_AMI_Payment]![Compared to National])
Prob2011-2014:
Sum(InStr([HQI_HOSP_Payment]![Category],”Greater”))/
Count([HQI_HOSP_Payment]![Category])
Prob2012-2015:
Sum(InStr([HQI_HOSP_PaymentAndValueOfCare4]![Payment Category],”Greater”))/
Count([HQI_HOSP_PaymentAndValueOfCare4]![Payment Category])
Prob2010-2013: Sum( InStr
([Compared to National],
“Greater”)) / Count (
[Compared to National])
Select Measure
Payment_30_AMI
2010-2013
2010-2013 from
HQI_HOSP_AMI_Payment
SELECT Count([Compared to National]) AS [Hospitals2010-2013]
, Sum(InStr([Compared to National],”Greater”)) /
Count([HQI_HOSP_AMI_Payment1]![Compared to National]) AS [Prob]
, [Measure Start Date]
, [Measure End Date]
FROM HQI_HOSP_AMI_Payment1
WHERE Denominator”Not Available” AND [Measure ID]=”PAYM_30_AMI”
GROUP BY Measure Start Date, Measure End Date;
2011-2014
Different File &
Fields
2011-2014
SELECT
Count(Category) AS [Hospitals]
, Sum(InStr([Category],”Greater”))/Count([Category]) AS [Prob]
, [Measure Start Date]
, [Measure End Date]
FROM HQI_HOSP_Payment
WHERE Denominator”Not Available” AND [Measure ID]=”PAYM_30_AMI”
GROUP BY [Measure Start Date], [Measure End Date];
2012-2015
2012-2015
SELECT Count([Provider ID]) AS Hospitals
, Sum(InStr([Payment Category],”Greater”))/Count([Payment Category]) AS [Prob]
, [Measure Start Date]
, [Measure End Date]
FROM HQI_HOSP_PaymentAndValueOfCare4
WHERE Denominator”Not Available” AND [Payment measure ID])=”PAYM_30_AMI”
GROUP BY [Measure Start Date], [Measure End Date];
Query Results
Hospitals
2010-2013
2424
Prob
2010-2013
0.157591
Measure Start Date
07/01/2010
Measure End Date
06/30/2013
Hospitals
2011-2014
2369
Prob
2011-2014
0.154073
Measure Start Date
07/01/2011
Measure End Date
06/30/2014
Hospitals
2012-2015
2343
Prob
2012-2015
0.108408
Measure Start Date
07/01/2012
Measure End Date
06/30/2015
Calculate Control Limits
Time Period
Number of Hospitals
Observed
Standard Deviation
Upper Limit
Lower Limit
Grand Average Rate
2010-2013
2424
0.158
0.007
0.161
0.119
0.140
2011 2014
2369
0.154
0.007
0.162
0.119
2012-2015
2343
0.108
0.007
0.162
0.119
Average Rate =SUMPRODUCT(Number Hospitals, Observed)/SUM(Observed)
Standard Deviation =SQRT((Average Rate*(1-Average Rate))/# of Hospitals)
Upper Limit =Average Rate + 3 * Standard Deviation
Lower Limit =Average Rate – 3 * Standard Deviation
Plot Control Chart
FEWER HOSPITALS EXCEEDED AVERAGE NATIONAL 30-DAY
AMI PAYMENTS.
Question 2: Comparison of Rates
Farrokh Alemi, PhD
COMPARISON OF RATES
QUESTION 1
Tiffany Woods
George Mason University
Instructor: Farrokh Alemi, Ph.D.
HAP 725
The Problem
We are asked to construct a control chart showing
the rate of prophylactic antibiotic overuse over
time at Southeast Alabama Medical Center.
Our data will come from the 2015 & 2016 datasets
which contain the most recent recordings of
antibiotic overuse.

Go to https://data.medicare.gov/data/archives/hospital-compare
Under the “2016 Annual Files” section, open the .zip file
Hospital_Revised_FlatFiles_20161110.

You will see a .csv file called Timely and Effective Care – Hospital.
*This file contains the data on antibiotic overuse.
Save this file as an Excel Workbook (.xlsx) & rename it according to its
date (2016-11-10)
Save as “2016-11-10”
Complete these steps for the following 9 .zip files:
2016 Annual Files
• Hospital_Revised_FlatFiles_20161110 (2016-11-10)
• HOSArchive_Revised_FlatFiles_20160810.zip (2016-08-10)
• HOSArchive_Revised_FlatFiles_20160504.zip (2016-05-04)
2015 Annual Files
• HOSArchive_Revised_FlatFiles_20151210.zip (2015-12-10)
• HOSArchive_Revised_FlatFiles_20151008.zip (2015-10-08)
• HOSArchive_Revised_FlatFiles_20150716.zip (2015-07-16)
• HOSArchive_Revised_Flatfiles_20150506.zip (2015-05-06)
• HOSArchive_Revised_Flatfiles_20150416.zip (2015-04-16)
• HOSArchive_Revised_Flatfiles_20150122.zip (2015-01-22)
End the end, you should have 9 .xlsx files saved:
Step 2: Import the 9 .xlsx files into Microsoft SQL Server
Use ‘Microsoft Excel’ as your data source
In the end, you should have
something similar to this:
*You may want
to rename
each dbo
according to its
date, as such.
Step 3: Query
the 9 tables
This code selects the
antibiotic overuse data
(Measure ID = SCIP_INF_3)
for Southeast Alabama
Medical Center (Provider ID
= 10001) from each table.
Then, it compiles this data
into a singular output using
UNION.
Query Output
– Provider ID: specifies Southeast Alabama Medical Center
– Measure ID: refers to prophylactic antibiotic use
– Measure Start Date/End Date: time frame from which data was collected
– Score: the # of patients with antibiotic overuse
– Sample: all surgical patients with no evidence of prior infection
Step 4: Transfer query results to Excel
Highlight the entire output, right click & select “Copy with Headers”
Open Excel & paste into a new spreadsheet
Step 5: Calculate rates & control limits
To calculate:
– Midway Point: ( [Measure Start Date] + [Measure End Data] ) / 2
*Remember to convert this result into a date format
– Rate of Overuse: [Score] / [Sample]
– Grand Rate: SUM (Score) / SUM (Sample)
– Upper Limit: Grand Rate + 1.96*SQRT((Grand Rate*(1-Grand Rate))/Sample)
– Lower Limit: Grand Rate – 1.96*SQRT((Grand Rate*(1-Grand Rate))/Sample)
Step 6: Plot the control chart
wider due to
a smaller
sample size
Analysis: There was a statistically significant increase in the rate of antibiotic
overuse for both the 4/1/15 and the 5/17/15 time frames. All other time
frames had rates that were within our control limits.
THANK YOU
Feel free to contact me if you have any questions!

attachment

#### Why Choose Us

• 100% non-plagiarized Papers
• Affordable Prices
• Any Paper, Urgency, and Subject
• Will complete your papers in 6 hours
• On-time Delivery
• Money-back and Privacy guarantees
• Unlimited Amendments upon request
• Satisfaction guarantee

#### How it Works

• Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
• Fill in your paper’s requirements in the "PAPER DETAILS" section.