TIPSHEET AND WEBINAR RECORDING: How to Mine California Hospital Data for Great Stories

This tipsheet accompanies "Hiding in Plain Sight: Mining California Hospital Data for Great Stories," a reporting webinar featuring Charles Ornstein and sponsored by ReportingonHealth and the Association of Health Care Journalists. Miseed this excellent Webinar? You can view it in several short videos at the end of this post.

California journalists are lucky to have the Office of Statewide Health Planning and Development. Despite its bureaucratic and jargony name, it is a terrific resource for facts and figures about health care (particularly hospital care) in the state. A look through its website (www.oshpd.ca.gov) can help you discover which hospitals in your community are most profitable, how much they charge for specific procedures, which perform the most C-sections and which are at the greatest risk of collapsing in powerful earthquakes. OSHPD keeps track of data from every region of California--the most urban and most rural.

Other states have similar health data warehouses. Texas has the Center for Health Statistics (http://www.dshs.state.tx.us/chs/hosp/), Pennsylvania has the  Pennsylvania Health Care Cost Containment Council (http://www.phc4.org).

This tipsheet will focus on the hospital utilization data on OSHPD's website and provide tips on how best to use the information.

To get started, go to the site (www.oshpd.ca.gov), click on Data & Reports in the top line, and then click on Hospitals. The direct link to the page is http://www.oshpd.ca.gov/HID/DataFlow/HospMain.html. A flow chart will appear. (See left)

Hospital utilization: This data helps tell you which hospitals are busiest and where they are focusing their resources. You can find these by clicking on the Data box on the main hospitals page or by going directly to http://oshpd.ca.gov/HID/DataFlow/HospData.html.

Click on the Utilization Pivot Profiles. They are at the bottom of the page. You can download the prior year's file or the files from previous years. The file is a .zip file so you'll have to unzip it before you'll see the Excel spreadsheet. Most computers do this automatically for you.  Click on the 2009 Pivot Profiles (the most recent as of early September 2011) and open the Excel file on your desktop. This report deals with use of services at a hospital. You'll find discharges, patient days and length of stay for various hospital units, details about the babies born there, lots of information about ER use and diversion of ambulances, and the types of surgeries performed.

Before you do anything else, save a clean copy of the spreadsheet. This is good practice whenever you're working with data.

Each file contains five worksheets: profile summary page, pivot selection page, data, glossary and notes.

Open the spreadsheet and you'll see a worksheet with pull down menus (left). 

Take a look around. Examine the choices in each dropdown menu.

Pick any hospital. Then, click on the profile summary tab at the bottom of the page.

You'll find a ton of information, neatly organized and grouped. At the top is information about number of beds, patient days, discharges and average length of stay. Below that are stats about particular services, such as births, emergency room and surgery.

This data can seem overwhelming, but a few things to keep in mind:

• You can select multiple hospitals through the pivot table, not just by name but by a variety of characteristics: for instance all the ones in a particular county, all the hospitals by ownership type (nonprofit, for profit) or all the teaching hospitals in the state. The profile page will automatically combine or average each data item.

• This is a one year (calendar year) snapshot. You'll want to look at previous years' data to spot trends. Is the hospital busier than in the past? Is the hospital performing more C-sections or turning away more emergency room patients than it once did? These are only questions that you can answer by looking at past pivot tables.

• Within the file you can find data on every hospital in the state. For that, click on the Data tab at the bottom of the page. Another, and perhaps better, option if you are looking at the raw data is to download the complete data file on the OSHPD site. That allows you to connect the data back with the questions on the reporting form.

The glossary tab is helpful.

Back in the pivot table, click on the Data tab. By sorting Column ER, you can see total ER visits by hospital. Arrowhead Regional, you'll find, was the busiest ER in the state last year. Kaiser Sacramento-Roseville/Morse had the most ER patients register and leave without being treated (Column EU). St. Bernardine Medical Center in San Bernardino performed the most cardiac catheterizations in the state (Column HC).

Hospitals submit their utilization information using a system called ALIRTS. You can search for each hospital's reports by going to http://www.alirts.oshpd.ca.gov/AdvSearch.aspx.

Remember, this is what hospitals have reported to OSHPD. Double check the data and give hospitals a chance to comment before you run the figures.

Last but not least, here is contact information for OSHPD Public Affairs. Feel free to tell him I sent you!

David Byrnes
dbyrnes@oshpd.ca.gov
(916) 326-3606

View the archived webinar in these videos. 

[video:http://www.youtube.com/watch?v=zPWKgYRp4-k]

[video:http://www.youtube.com/watch?v=Zgjl9MHF4E4]

[video:http://www.youtube.com/watch?v=jxm4KuY0qsk]

[video:http://www.youtube.com/watch?v=OiJtbITfA4w]

[video:http:http://www.youtube.com/watch?v=y8DfslkCxeA]

[video:http://www.youtube.com/watch?v=sBrOT-qTVHE]

You can also find this tipsheet and video of the webinar at the Association of Health Care Journalists.

Home page photo credit: LAC+USC Department of Emergency Medicine