Tip Sheets

Hiding in plain sight: California hospital data

Charles Ornstein, ProPublica
Charles.ornstein@propublica.org

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  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, Pennsylvania has the Pennsylvania Health Care Cost Containment Council.

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. (Direct link to the page.) 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 (If you see preliminary data, it may not have a pivot table yet.) 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 2011 Pivot Profiles (the most recent as of mid-2013) 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.

Working with the 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 (right).  

You may notice at the top that there's a security warning. If so, click options and enable macros.

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. Even more helpful is the documentation (a blank form) that the hospitals fill out. They are on the same page as the pivot tables. An example: http://www.oshpd.ca.gov/HID/ALIRTS/Text_pdf_files/Hosp_Forms_Inst/hosfrm11.pdf.

Back in the pivot table, click on the Data tab. By sorting Column EQ, you can see total ER visits by hospital. Los Angeles County-USC Medical Center, you'll find, was the busiest ER in the state that year. Community Regional Medical Center-Fresno had the most ER patients register and leave without being treated (Column ET). Sharp Memorial Hospital performed the most angioplasties with stents in the state (Column HA).

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. The ALIRTS report includes details on newly purchased hospital diagnostic and therapeutic equipment (think DaVinci robots). That’s under Section 6, Major Capital Expenditures.

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.

Exercise

What is the busiest ER in your county? Which hospital had the most patients leave without being examined? Extra credit: How does this compare to previous years?

Contact

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