Constructing Control Chart for Prophylactic Antibiotic Overuse Rate at Southeast Alabama Medical Center

Slide Note

In this project, we need to create a control chart showing the rate of prophylactic antibiotic overuse over time at Southeast Alabama Medical Center. The process involves downloading data from Medicare, importing it into Microsoft SQL Server, and querying the tables to extract specific information for analysis.

gmad Follow

Uploaded on Sep 06, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

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.

- *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) You will see a .csv file called Timely and Effective Care Hospital. 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.