The Crain’s Report was developed with the SPARCS dataset. The Statewide Planning and Research Cooperative System (SPARCS) contains data about all inpatient hospital discharges in New York, recording each hospital visit in an “All-payer” database. The dataset includes patient demographics, length of stay, procedures performed, diagnoses, and financial information. The dataset is historically available through special data use agreements, but a de-identified “open data” version are now publicly available on Health Data NY. Data journalists used these data to identify major disparities in the cost of hip replacements.

Exploring the SPARCS dataset in SOCRATA.

Socrata is one of the most popular platforms to publish Open Data. You already have used Socrata exploring Helath Data NY in the Scavenger Hunt. It is the application that you used to see data elements in a tabular form.

To explore the SPARCS dataset in Socrata,

1. go the Health Data NY website following the URL: https://health.data.ny.gov, or google “Health Data NY”
2. Type “SPARCS 2014” into the search bar
   
3. Select “Hospital Inpatient Discharges (SPARCS De-Identified): 2014” Dataset
   
   
4. Click on View Data
   
5. Explore both the Data and Meta Data and think about the following questions

Downloading SPARCS into Excel.

The SPARCS dataset is very large. Although we could not call this dataset Big Data, it is large enough to make Excel and your computer very slow. In this way, we are going to download only the Hip’s related data in this dataset. Follow the instructions from SOCRATA (the tabular view of SPARCS dataset – there is also a video in Blackboard that shows the same procedure):

1. Click “Filter”
   
2. Click on “Add a New Filter Condition”
   
3. Select “CCS Procedure Code”
   
4. Set procedure code to 153 and hit “Enter” in your keyboard. These numbers are standard medical billing codes; the same codes are used throughout the US by all medical facilities. The code 153 is for Hip Replacement.
   
5. Click on “Export”
   
6. Download as “CSV for Excel”
   
7. Depending on the browser that you are using, different things may happen. You may get a dialogue box where you need to select “Open with Microsoft Excel,” or you may get the file in the status bar of the browser. Anyways, open it with Excel.

Exploring SPARCS into Excel.

1. A Cardinal rule of data management: DO YOUR ANALYSIS USING A COPY! Keep your original dataset in a safe place and do not touch it, ever. In this way, “Save as” SPARCS 2014 Practice Dataset as an “Excel Workbook” and not as “CSV.” This is your original file, not to be touched. Make a new copy for this tutorial called “Tutorial 1.”
2. Use “Freeze Panes” and the Filter tool to explore the dataset. Also look at the metadata and answer the following questions

3. Delete columns we don’t need for our analysis in the following tutorials. This step is not critical, but helpful for a large dataset like this (There is a short video tutorial including steps 3, 4 and 5 in this document). DELETE ALL THESE COLUMNS: Operating certificate number, facility ID, ZIP code, discharge year, CCS procedure code, CCS procedure description, APR DRG code, APR DRG description, APR MDC code, APR MDC description, APR severity of illness code, payment typology 2, payment typology 3, attending provider license number, operating provider license number, other provider license number, birth weight, abortion exit indicator, emergency department indicator.
4. Create a new variable (Cost-to-Charge Ratio), with the formula Total Costs / Total Charges. With this measure, lower values of the ratio correspond to higher profit margin.
5. Create a new worksheet called “Calculations,” and create the following table:

Attribution

By Erika Martin and Luis F. Luna-Reyes, and licensed under  CC BY-NC-SA 4.0.