MEDITECH SSIS+SQL+DR Tip: Using CMS ICD-10 GEM data

CMS Does Not Make Things Easy

Sometimes you just have to wonder… New ICD-10 diagnosis and procedural coding has been brought into play in the United Status (October 1, 2015 for diagnosis codes; next year for procedure codes), and CMS has — though a substantial effort — identified sets of equivalent codes between the -9 and -10 code sets. Your first thought may be, “This is huge! A tremendous help!” (If you want more details about this effort and you have not seen it already, check out the GEM website for Diagnoses and Procedures).

But on closer examination, you’ll find some disappointing news. The GEM files look like this:

0010   A000    00000
0011   A001    00000
0019   A009    00000
0020   A0100  10000
0021   A011    00000
0022   A012    00000
0023   A013    00000
0029   A014    00000
0030   A020    00000
0031   A021    10000
00320 A0220  00000
00321 A0221  00000

Which seems a bit strange, because when they put together the text files that map -9 to -10 and back again, they represented the codes without embedded decimal points. This is troubling, because unless you include the leading zeroes, the embedded decimal points, various prefixed letters, and so on, there are duplicates between -9 and -10 codes.

Then, for comparing to MEDITECH data in ABS, we generally find that the decimal points are stored in the data, like this:

812.00      FX UP END HUMERUS NOS-CL
812.01      FX SURG NCK HUMERUS-CLOS
812.02      FX ANATOM NCK HUMERUS-CL
812.03      FX GR TUBEROS HUMERUS-CL
812.09      FX UPPER HUMERUS NEC-CL

Which means we’d need to manipulate the CMS GEM data into some format (or the ABS data) to make it match up. This is more work than we like to do.

Then (yes, there is more then), the equivalency tables don’t have any descriptions included, which makes it a bit hard to use them without referring to another table or another website. This isn’t a deal-breaker, but introduces more friction into the process of using them, which we could do without.

What did we do? Well… we automated the process!

1. We converted the fixed-width SDF-style text files into tab-delimited text files.
2. We loaded the tab-delimited text files into staging tables in a handy DR database.
3. We used the loaded CMS-provided data to match against the ICD-10 diagnosis and procedure codes already present in the DR and built new tables that contained the cleaned-up, descriptions-added, codes-properly-represented data.

The new tables look like this:

Icd09Code	Icd09Name	Icd10Code	Icd10Name	Flags	FlagMeaning
001.0	CHOLERA D/T VIB CHOLERAE	A00.0	CHOLERA DUE TO VIBRIO CHOLERAE 01, BIOVAR CHOLERAE	00000	Approximate Match | Mapped | Combination | Clustered | Choice List
001.1	CHOLERA D/T VIB EL TOR	A00.1	CHOLERA DUE TO VIBRIO CHOLERAE 01, BIOVAR ELTOR	00000	Approximate Match | Mapped | Combination | Clustered | Choice List
001.9	CHOLERA NOS	A00.9	CHOLERA, UNSPECIFIED	00000	Approximate Match | Mapped | Combination | Clustered | Choice List

Now this is something we can use much more easily!

Using the GEM Data

Once the data was in custom tables in the DR, we could query against it, which meant that building lists of ICD-10 codes for reports and extracts from the existing lists of ICD-9 codes was a lot easier.

Here is a [zip archive] with a number of files. Among them are four Excel-based representations of this cleaned-up, reformatted, and expanded data:

GemDxIcd09ToIcd10.xlsx	Lists ICD-9 Diagnoses codes and their ICD-10 equivalents
GemDxIcd10ToIcd09.xlsx	Lists ICD-10 Diagnoses codes and their ICD-09 equivalents
GemPxIcd09ToIcd10.xlsx	Lists ICD-09 Procedure codes and their ICD-10 equivalents
GemPxIcd10ToIcd09.xlsx	Lists ICD-10 procedure codes and their ICD-09 equivalents

You could start using this right away. But, that’s not actually the most efficient way to get at this data. The fastest, most helpful way to search and retrieve this data is by doing what we did – store this info in custom tables on your DR database server. Here’s how:

First, run this script:

IatricCreateGEMEquivalenceTables.sql

Note! This script assumes you have a custom database catalog on your DR server called zcus. If you do not, you’ll need to update the script appropriately before running it.

Then, once the tables to hold the data have been created, you can load up and run these scripts to populate the table(s):

InsertDataTo_GemDxIcd09ToIcd10_Table.sql
InsertDataTo_GemDxIcd10ToIcd09_Table.sql
InsertDataTo_GemPxIcd09ToIcd10_Table.sql
InsertDataTo_GemPxIcd10ToIcd09_Table.sql

Then, you can directly query the GemDxIcd09ToIcd10 table, for example:

SELECT MAX('ICD-10')      AS DxCodeType
            ,DAD.Icd10Code   AS DxCodeID
            ,DAD.Icd10Name  AS DxCodeName
FROM    zcus.dbo.GemDxIcd09ToIcd10 DAD
WHERE  DAD.Icd10Code <> 'NoDx'
             AND
             -- These examples are for a long-bone fracture report
             (
                    DAD.Icd09Code BETWEEN '812.00' AND '813.93'
                    OR
                    DAD.Icd09Code BETWEEN '821.00' AND '821.39'
                    OR
                    DAD.Icd09Code BETWEEN '823.00' AND '823.92'
                    OR
                    DAD.Icd09Code BETWEEN '827.0' AND '827.1'
             )
GROUP BY DAD.Icd10Code, DAD.Icd10Name

And get a list of equivalent ICD-10 codes:

DxCodeType	DxCodeID	DxCodeName
ICD-10	S52.539A	COLLES' FRACTURE OF UNSP RADIUS, INIT FOR CLOS FX
ICD-10	S52.539C	COLLES' FRACTURE OF UNSP RADIUS, INIT FOR OPN FX TYPE 3A/B/C
ICD-10	S52.539B	COLLES' FRACTURE OF UNSP RADIUS, INIT FOR OPN FX TYPE I/2
ICD-10	S52.043B	DISP FX OF CORONOID PRO OF UNSP ULNA, 7THB
ICD-10	S52.043C	DISP FX OF CORONOID PRO OF UNSP ULNA, 7THC
ICD-10	S52.043A	DISP FX OF CORONOID PROCESS OF UNSP ULNA, INIT FOR CLOS FX
ICD-10	S42.253B	DISP FX OF GREATER TUBEROSITY OF UNSP HUMER, INIT FOR OPN FX
ICD-10	S42.253A	DISP FX OF GREATER TUBEROSITY OF UNSP HUMERUS, INIT
ICD-10	S52.123A	DISP FX OF HEAD OF UNSP RADIUS, INIT FOR CLOS FX
ICD-10	S52.123C	DISP FX OF HEAD OF UNSP RADIUS, INIT FOR OPN FX TYPE 3A/B/C
ICD-10	S52.123B	DISP FX OF HEAD OF UNSP RADIUS, INIT FOR OPN FX TYPE I/2
ICD-10	S42.453A	DISP FX OF LATERAL CONDYLE OF UNSP HUMERUS, INIT FOR CLOS FX
ICD-10	S42.453B	DISP FX OF LATERAL CONDYLE OF UNSP HUMERUS, INIT FOR OPN FX
ICD-10	S42.433A	DISP FX OF LATERAL EPICONDYLE OF UNSP HUMERUS, INIT

Let’s discuss your report conversion workflow for a moment… Using the GEM data is an excellent start to getting your reports converted, but you should never just plug and play with these codes in a production report. Instead, use the GEM data to get a starting list of codes, and then provide those codes to your HIM team to review and approve. Then, use the approved codes in your report.

Which brings us to the code examples in this script, which are also part of the attached package:

IatricUsingGEMIcdEquivalenceTables.sql

There are examples in this code showing two main patterns with three layouts:

• Converting ICD-9 DX and PX to ICD-10
• Converting ICD-10 DX and PX to ICD-9

Each layout or code snippet generates the matching sets of ICD-9 and ICD-10 codes and puts them into one result set:

1. First, the example we just showed above, where it’s suitable to pasting into Excel and sending off to HIM to validate.
2. Second, a chunk of code that generates a list suitable for using with an IN (…) clause in your select, for example:

AbsDiagnoses.DiagnosisID IN
(
  '823.92'   -- FX TIBIA W FIB NOS-OPEN [ICD-09]
  ,'827.0'    -- FX LOWER LIMB NEC-CLOSED [ICD-09]
  ,'827.1'    -- FX LOWER LIMB NEC-OPEN [ICD-09]
  ,'S42.209A' -- UNSP FRACTURE OF UPPER END OF UNSP HUMERUS, INIT FOR CLOS FX [ICD-10]
  ,'S42.209B' -- UNSP FRACTURE OF UPPER END OF UNSP HUMERUS, INIT FOR OPN FX [ICD-10]
  ,'S42.213A' -- UNSP DISP FX OF SURGICAL NECK OF UNSP HUMERUS, INIT [ICD-10]
)

Where, as you see, we’re leveraging the ability to generate SQL code from our SQL query, and we’re including automatically generated comments that tell us the meaning of each code and the code set it came from.

3. Third, a code example that creates a #temp table and then populates it with both the ICD-9 and -10 codes, allowing us to then JOIN against the #temp table like so:

AbsDiagnoses.DiagnosisID IN ( SELECT DxCodeID FROM #tDx )

When should you use the IN (…) list versus the #temp table? That depends on how many times you’re using the list in one stored procedure:

• If you’re using the list of codes only once, and it’s not too giant, then use the IN (…) structure.
• Otherwise, use the #temp table approach.

Implementing the SSIS Package

The third way that you can get the GEM codes into custom tables in your DR is to use the SQL Server Integration Services 2012 package in the \Loader folder in the zip attached to the tip. This lets you load (or reload) codes directly from text files (one for DX and one for PX) into the custom tables.

The SSIS package is controlled (and customized) by a set of variables defined at the top level of the package. To use the package, you’ll want to unzip the archive into a folder, then fire up SQL 2012 Data Tools and open the loader.sln solution file.

You’ll be asked for a password at some point, it is “iatric”. After the solution has opened, open this package:

IatricLoadCmsGemsToDr.dtsx

When the package loads, it will complain that it cannot connect to your DR. That is because the package has no idea where you DR is, what it is named, or how to connect. But you can fix all that… In the Variables pane on the left (if it’s not there, do View menu > Other Windows > Variables ), there are a set of “control” variables that tell the package where everything is:

Variable	Configuration Notes
cDrServer	The DNS Name of your DR server to load the data.
cDrUserName	If you connect to your DR server with a SQL-only login, set that username here.
cDrUserPassword	If you connect to your DR server with a SQL-only login, set that password here.
cCustomCatalog	The new custom tables got created somewhere, put that catalog name here (like zcus).
cNprCatalog	In a C/S or Magic DR setup, this is just the name of your LIVE database (like livedb). In a MT6 or 6.1 environment, it’s catalog name where MIS and ABS live.
cDxGemFolder	Folder path to where the GEM diagnosis files wound up (no \ at the end).
cPxGemFolder	Folder path to where the GEM procedure files wound up (no \ at the end).
cMisDatabaseID	The SourceID of your MIS database where DMisDiagnoses lives.
cIcd09DxVersionID	Defaults to “32”, but check your DMisDiagnosis dictionary to see if you have a different value.
cIcd10DxVersionID	Defaults to “32icd10”, but check your DMisDiagnosis dictionary to see if you have a different value.

If you’re connecting to your DR server via integrated AD authentication, then right-click on the DR Connection Manager (bottom middle of the screen), pick Edit, and change the “Log onto the server” setting to Use Windows Authentication.

Once those settings are configured, run the package. Note that you need to have run the table creation script mentioned above first, and note that the package will automatically clear both the staging and final / production tables before reloading.

If you need help…

Our Report Writing Services team can help you get this set up. Just contact your Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss.