This exercise allows you to examine how quality of hospital care is evaluated through data obtained from electronic health records. This exercise is discussed in several health administration courses, including courses on quality improvement, informatics, or statistics. In practice, the need to analyze these types of data arises in improvement teams, where managers, clinicians and sometimes patients work together to systematically improve healthcare processes.Part 1: In this assignment, you are asked to examine if prophylactic antibiotics are discontinued within 24 hours after end of surgery. Instead of providing you with the data, we ask that you download the data on the web so that you learn more about the source of the data and peculiar organization of the data. Hospital Compare provides information on many quality indicators. In this assignment, you are asked to focus on failure to stop use of antibiotics post surgery. The Hospital Compare web site provides the following rationale for why this measure of quality is helpful: “A goal of prophylaxis with antibiotics is to provide benefit to the patient with as little risk as possible. It is important to maintain therapeutic serum and tissue levels throughout the operation. Intra-operative re-dosing may be needed for long operations. However, administration of antibiotics for more than a few hours after the incision is closed offers no additional benefit to the surgical patient. Prolonged administration does increase the risk of clostridium difficile infection and the development of antimicrobial resistant pathogens.” To complete this assignment complete the following tasks:Download Hospital Compare data for the years starting from 2015 till now. Merge the file “HQI_HOSP_TimelyEffectiveCare” across all databases for different years.Select measure ID “SCIP_INF_3”; this measure refers to prophylactic antibiotic use. Select to work with data from “Southeast Alabama Medical Center”; this is provider ID “010001”.Calculate rate of overuse of antibiotics. In these data, the field “Score” refers to the number of patients with overuse of antibiotics. The field “Sample” refers to all selected surgical patients with no evidence of prior infection. The ratio of Score to Sample provides the rate of overuse of antibiotics. Construct a control chart showing rate of overuse of prophylactic antibiotic over time in Southeast Alabama Medical Center. Download the needed data using the following databases (if new data is available, please use them)You can find the solution here here – Alternative Formats .The student peer video shows that data across the years us merged using SQL. You can do the same merger without using SQL. Open the CSV files (you can ignore access files for old years.) Filter the data by the measure you are interested in Copy the sheet with the relevant filtered data from each CSV file into a single sheet within the new Excel file. In a sheet titles “ALL” inside the Excel file, use cell reference to bring the data in different sheets to the “ALL” sheet. Do your analysis on this sheet. Part 2: Nursing home and hospital administrators implement many initiatives to reduce falls. This problem shows how these initiatives can be measured and evaluated. Prepare a risk-adjusted control chart for dataon falls in a nursing homePlease use the following peer video as a reference for this assignment. You can find the solution here here – Alternative Formats .Part 3: The following data were obtained on post surgical infection rates. Are we having more infections than expected from the patients’ conditions?You can find the solution here here – Alternative Formats .WeekRisk of Infection for each PatientNumber Infected10.90.80.70.80.90.85620.70.80.70.60.80.9530.80.950.920.87440.50.60.660.67350.30.40.50.40.50.34260.30.40.51Part 4: Download Hospital Compare data. In these files the denominator indicates the number of patients. Whether the hospital had above average payments are reported in different fields: [Compared to National], [Category], and [Payment Category]Select data that meet the following conditions:Select the “PAYM_30_AMI” measureSelect hospitals that in the denominator do not have “Not Available”.Construct a control chart showing probability of change over time. To assist, we have download some of the necessary files. You can add to these additional more recent files from the Hospital Compare site. Since the data on AMI payments does not change in the files, you can save time and read only the following files into a new database:FileDatabaseFieldStartEndHQI_HOSP_AMI_Payment HOSArchive_20150122Compared to National20102013HQI_HOSP_Payment HOSArchive_20150716Category20112014HQI_HOSP_PaymentAndValueOfCare HOSArchive_20161110Payment Category20122015Include other data for more recent years after 2016You can find the solution here here – Alternative Formats and here here – Alternative Formats .Some of you do not have Microsoft Access. Early files in Hospital Compare used Microsoft Access and later files used CSV (a file format that opens with Excel.) To remedy this situation, I am replacing Microsoft Access files with Excel Files. If you have to look at only AMI quality measures you need to use the filter feature within Excel. Use these files: HQI_HOSP_PaymentAndValueofCare20161110 HQI_HOSP_AMI_Payment20150122 HQI_HOSP_Payment20150716Question 4: Comparison of Rates
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.
Download, Unzip, Rename Data
1. Download
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.
Step 1: Download the data
–
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
Limits spread
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!
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- 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.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.