BLOGS
EASY SUBSCRIBE


Report Writing

Interoperability and EHR Optimization

Privacy and Security

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

Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems

CMS 1CD-10 GEM data.jpgCMS 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 Karen Roemer (karen.roemer@iatric.com) to discuss.

Topics: Report Writing Services, SQL Tip, ICD-10, MEDITECH, DR Tip, SSIS Tip

Subscribe to the Report Writing blog.

Receive the latest articles directly in your inbox.
Enter your email address and click SUBSCRIBE:

Comments

0 COMMENTS