Import Medicare Provider Utilization Data into Postgres

README.md

Instructions for importing the recently released medicare data...

http://www.cms.gov/Research-Statistics-Data-and-Systems/Statistics-Trends-and-Reports/Medicare-Provider-Charge-Data/Physician-and-Other-Supplier.html

• Download the tab-delimited file
• Unzip the file
• tail -n +3 Medicare-Physician-and-Other-Supplier-PUF-CY2012.txt > /tmp/medicare_data.txt
• psql < import.sql

So easy! :)

Once you're done, feel free to create some interesting queries.

import.sql

DROP TABLE IF EXISTS medicare_import;
CREATE TABLE medicare_import (
  npi                              varchar(10),
  nppes_provider_last_org_name     varchar(70),
  nppes_provider_first_name        varchar(20),
  nppes_provider_mi                varchar(1),
  nppes_credentials                varchar(20),
  nppes_provider_gender            varchar(1),
  nppes_entity_code                varchar(1),
  nppes_provider_street1           varchar(55),
  nppes_provider_street2           varchar(55),
  nppes_provider_city              varchar(40),
  nppes_provider_zip               varchar(20),
  nppes_provider_state             varchar(2),
  nppes_provider_country           varchar(2),
  provider_type                    varchar(43),
  medicare_participation_indicator varchar(1),
  place_of_service                 varchar(1),
  hcpcs_code                       varchar(5),
  hcpcs_description                varchar(30),
  line_srvc_cnt                    double precision,
  bene_unique_cnt                  double precision,
  bene_day_srvc_cnt                double precision,
  average_medicare_allowed_amt     double precision,
  stdev_medicare_allowed_amt       double precision,
  average_submitted_chrg_amt       double precision,
  stdev_submitted_chrg_amt         double precision,
  average_medicare_payment_amt     double precision,
  stdev_medicare_payment_amt       double precision
);

COPY medicare_import FROM '/tmp/medicare_data.txt';