Monday, December 7, 2020

Medicare's National Provider Cloud Data: Exploring the Aggregated "Provider Type" View

OK, I realize that I've just written the most boring blog title possible.  

But there's the summary.  Medicare released huge cloud databases of every Part B CPT code and code volume paid to every physician and laboratory provider.  CMS released the 2018 version in November 2020 (here).  For example, you can see every lab that billed for code 81162, or 81445, or any other code.  But there's another data view aggregated by provider (e.g. by laboratory).  Let's explore that.


If you go to the home page for CY2018 provider data here, the top links are for Detailed Data - e.g. every doctor, every lab, every CPT code they were paid for.  The full table is about 10M lines.

But there are some other links of interest further down.  One is "Provider Summary Table."  I couldn't get the claimed tab delimited format zip file to open, but I opened the cloud version and downloaded the whole thing in about 300 MB.  Then I filtered only for Clinical Labs and downloaded that, which is 2800 lines and 1 MB.  

Further down, you can get aggregate data by National and by State Level.  This could be very useful - in the past, I've literally added up CPT code data from 50 individual state Excel files available elsewhere on the CMS website.  The state level data is probably much easier to manipulate from this starting point. [FN1]

Let's go back to the Provider Summary Table.  The whole table has 1M lines, and most people won't want to research every doc in the US most of the time.  Instead. I filtered on the field Provider Type for Clinical Laboratory.  This yielded a manageable file with about 2800 lines.   I duplicated the main page in Excel, and sorted it by Dollars Allowed.  Note that really big labs like Quest and Labcorp bill via a lot of NPI locations, although Labcorp is somewhat more consolidated in its billing than Quest.  You'd have to do some extra work to aggregate the multiple entries for, say, Quest, into a single view of Quest Medicare payments.   

But let's start with the biggest dollar entity, LABCORP under NPI '4910.  We discover that in 2018 it submitted 768 different HCPCS codes for 16M different services for 1.7M beneficiaries.  It charged $1.2B and was paid $266M.  


click to enlarge

I get the cumulated dollars allowed over 2800 labs as $5.7B.  Of that, from the above table, the top line Labcorp value is 4.7%, the next line is Exact Sciences with 3.0%, the next line another Labcorp line with 2.1%, and the fourth line a Quest line also with 2.1%.  

If you go fat to the right in the table, not shown in the JPEG below, you can get columns of data on the number of patients by age group and the number of patients by racial group and other characteristics.  The top Labcorp line item served exactly 827,293 benes age 65-74, 473,839 age 75-84, and 169,764 benes over age 85.  1,304,374 were white, 311,486 African-American, and so on.  


Excel Nerd Note

This gives you one handy source of all lab industry NPIs and entity names.  According to a recent communication I got from the FOIA office at CMS, they are willing to release data such as MolDx files by limited data columns including NPI, test name, and price (if locally set by the MAC).   One could do an Excel function like merge/index/Vlookup on the {NPI-lab name} data shown above and the {NPI-test} name in the edit file, and generate a new table with each line including NPI plus lab name plus test name  in one place.  



The cloud database currently has state level cloud data to 2018, whereas elsewhere you can get state by state single Excel files for 2019.