MedlinePlus Connect: Planning for Clinical Coding System Changes

APPENDIX F: ACCESS & EXCEL TIPS & INSTRUCTIONS

The bullets and tables below provide information about where to find all necessary files and how each of the files and tabs was used for analysis.

1. 
   All files used for analysis are available here: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings (explained below)
   
2. 
   The files used for the Access database, as well as the Access database are available here: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access (explained below)
   

DOCUMENT KEY

• 
  Excel Analysis Documents
  

in: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings

Document Name	ICD9_10sample_2011 0722.xlsx (link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\ICD9_10 sample 2011 0722.xlsx)
Description	I used this spreadsheet for the random sample analysis and the selective sample analysis. This spreadsheet contains a number of tabs described below.
Tabs	Randomsample95: Tab used for analysis of the sample data (95% confidence level, 5% margin of error). Data pulled from forward mapping file in Access. Added columns for Naomi’s comments, M+ Problem, Problem Note, and Problem Category. Key: Defines the numbers used for the M+ Problem column. Pivot: Tab includes pivot table analysis used to create a table in the report. Sample (S00-T89): Tab includes all code pairs reviewed for the selective sample. Data pulled from Sxx and Txx tabs which include all code pairs from S00-T89 chapter that map to a health topic. Original (M+): Tab includes all forward mapping code pairs that map to a Health Topic. The sample was pulled from this data. Original (all): Tab includes all forward mapping code pairs (with and without Health Topic mappings). The Original (M+) tab was created from this data. Numbers&stats: Tab includes stats used to calculate the sample. Sorted numbers were the randomly selected numbers from StatTrek used to create the random sample. Mxx – Zxx: Tabs provide all code pairs from MXX – ZXX sections. These were not used but I left them in case they’re useful for future research. Sxx and Txx tabs include old problem category names.

Document Name	ICDGEMsCombined_2011 0723.xlsx (link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\ICDGEMsCombined_2011 0723.xlsx)
Description	I used this spreadsheet as the “master file” with both forward and backward GEMs file that contain mappings to Health Topics. Individual forward and backward files are also included and both are color coded.
Tabs	Pivot: This tab includes pivot table analysis and a graph I created for the report. It indicates the number of code pairs within each ICD-10-CM chapter in each file (forward/backward) as well as the total number of code pairs from both files within each ICD-10-CM chapter. Combined: This tab provides the combined master file of both forward and backward mapping files. Backward mapping files (10 to 9) are grey in color. Forward mapping files (9 to 10) are orange in color. The original file (backward or forward) is also indicated in the column titled “File”. This contains all code pairs with a Health Topic mapping. 10 to 9: This tab includes all backward mapping files with a Health Topic mapping. I imported these from Access. They are all grey in color. 9 to 10: This tab includes all forward mapping files with a Health Topic mapping. I imported these from access. They are all orange in color. Duplicates: This tab provides all duplicates identified from the combined file. I did not use this file but thought it useful since I could view what was the same between the two files.

• 
  Access Analysis Documents
  

in: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access

Document Name	9cm to 10cm gem.txt (link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\9cm to 10cm gem.txt)
Description	Forward mapping text file downloaded from CMS website. File available from https://www.cms.gov/ICD10/11b1_2011_ICD10CM_and_GEMs.asp .Within the Downloads section, select 2011 Diagnosis Code Set General Equivalence Mappings [ZIP]. File was imported into Access. Contains all ICD-9-CM codes.

Document Name	10cm to 9cm gem.txt (link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\10cm to 9cm gem.txt)
Description	Backward mapping text file downloaded from CMS website. File available from https://www.cms.gov/ICD10/11b1_2011_ICD10CM_and_GEMs.asp . Within the Downloads section, select 2011 Diagnosis Code Set General Equivalence Mappings [ZIP]. File was imported into Access. Contains all ICD-10-CM codes.

Document Name

ICD9CM_2010AB_UMLS.xls Link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\ICD9CM_2010AB_UMLS.xls

Description

ICD-9-CM description files from Kin Wah Fung, Lister Hill Center. I requested this file from Kin Wah since I could not find recently updated descriptions of ICD-9-CM online. This file includes all Procedure (format XX.XX) and diagnosis (XXX.XX…) codes from ICD-9-CM. The procedure codes need to be extracted which means that all codes with format XX.XX (only 2 characters before the period) need to be removed. Only diagnosis codes will remain (diagnosis codes have 3 characters before the period). Once the procedure codes are all removed, all periods should be removed from the remaining Diagnosis codes. File uploaded into Access to provide descriptions of ICD-9-CM codes in the GEMs mappings.

Document Name	Icd9complete May 11-2011.xlsx Link:P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\icd9complete May 11-2011.xlsx
Description	This file is available from the MedlinePlus Connect team. It provides ICD-9-CM codes and the corresponding Health Topics as mapped by the MedlinePlus Connect team. File was uploaded into Access to provide Health Topic Mappings to associated ICD-9-CM codes.

Document Name	ICD10CM_descriptions_may 11 2011.xlsx Link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\ICD10CM_descriptions_may 11 2011.xlsx
Description	This file contains the ICD-10-CM descriptions as imported from the CMS website (see next file). First Step: download text file. Second Step: Import into Excel (make sure all descriptions and codes import well). Third Step: Import into Access. This file provides all ICD-10-CM descriptions within Access.

Document Name

ICD-10-CM code descriptions.txt Link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\ICD-10-CM code descriptions.txt

Description

ICD-10-CM code description text file from CMS website. File available from https://www.cms.gov/ICD10/11b1_2011_ICD10CM_and_GEMs.asp .Within the Downloads section, select 2011 Code Descriptions – Long Format, Table Format, and README files [ZIP]. File imported into Excel and then Access. This was done to improve how the file imported since the full description did not originally import well to Access directly from the text file.

Document Name	MedlinePlusConnect 2011 0711.accdb Link: P:\Health\MPlusConnect\ICD10_Planning\GEMsMappings\access\MedlinePlusConnect 2011 0711.accdb
Description	The Access database used to pull in and connect the various files to create the final documents for analysis. The database was used to relate the various text files and provide all codes, descriptions, GEMs flags, and associated Health Topics. The main two items of use and interest from the database are the following queries (described below): “10 to 9 Query – 2011 May 1” and “9 – 10 query – 2011 May 10”.
Tables (tables not used for final analysis in italics)	10cm to 9cm gem: Backward mapping file imported from the GEMs text file. During import, created separate columns for each flag and added a new column for perfect flag. Therefore, if the first flag was a 0 (indicating identical code pairs), the perfect column had a 1 and the approximate column had a 0. If the first flag was a 1 (indicating an approximate code pair), the perfect column had a 0 and the approximate column had a 1. The numbers expressed for the 2^nd through 5^th flags were directly translated to their corresponding columns. For example, if Flags=0, Perfect Flag column will have a 1, all other columns will have 0. If Flags=10000, Perfect Column will have 0, Approximate Column will have 1, all others will have 0. I also kept a column with the flags in their original state to doublecheck the flags while working. 9cm to 10cm gem: Forward mapping file imported from the GEMs text file. During import, created separate columns for each flag and added a column for perfect flag. For example, if Flags=0, Perfect Flag column will have a 1, all other columns will have 0. If Flags=10000, Perfect Column will have 0, Approximate Column will have 1, all others will have 0. Also kept the normal flag column. ICD10CM Descriptions May 13 2011: File imported from Excel document. Excel document created from text file downloaded from CMS website (see Excel file of same name above). Icd9cm 2010ab umls: old version of ICD-9-CM description file. Have not deleted in case it is linked to any necessary documents. Icd9cm 2010ab umls – May 13: Current version of ICD-9-CM description file. File imported from Excel file of same name. May11,2011 – ICD9 to M+ Topics: MedlinePlus Health Topic file with ICD-9-CM codes, ICD-9-CM code descriptions, and mapped Health Topics. Imported from MedlinePlus Connect excel sheet titled Icd9complete May 11-2011.xlsx (see above). Will need to be updated with most recent version. Two-way matches with 10IDs: Provides a list of all code pairs that are the same between the forward and backward mapping files. Code pair ID is based on the backward mapping file. Two-way matches with 9IDs: Provides a list of all code pairs that have the same ICD-9-CM codes. Code pair ID is based on the forward mapping file. Unmatched 10cm to 9cm gem: Provides a list of all code pairs that are unique to the backward mapping file. Unmatched 9cm to 10cm gem: Provides a list of all code pairs that are unique to the forward mapping file.
Queries (queries not used for final analysis in italics)	10 to 9 Query – 2011 May 1: Backward mapping file ultimately used for analysis (after imported into Excel). Includes the following columns: ICD10CM Code, ICD10CM Description, ICD9CM Code, ICD9CM Description from UMLS, ICD9CM Description from M+Connect file, MedlinePlus Topics 1-3, Flags, Perfect Flag, Approximate Flag, No Map Flag, Combination Flag, Scenario Flag 9 – 10 query – 2011 May 10: Forward mapping file ultimately used for analysis (after imported into Excel). Includes the following columns: ICD9CM Code, ICD9CM Description (UMLS and M+Connect), ICD10CM Code, ICD10CM Description, MedlinePlus Topics 1-3, Flags, Perfect Flag, Approximate Flag, No Map Flag, Combination Flag, Scenario Flag Find duplicates for May11,2011 – ICD9 to M+ Topics: I ran this query to find all duplicates within the Health Topics file (May11,2011-ICD9toM+Topics). This is purely to make sure there are no errors in that file that will impact the 10 to 9 and 9 to 10 queries. I only used this at the beginning, before creating the previous 2 queries. Two-way matches with 10IDs Query: This finds code pairs that are the same in both the forward and backward mapping files. I did not use this query. Unmatched 10cm to 9cm gem without matching 9cm to 10cm: This finds all unique (or unmatched) code pairs in the 10 to 9 file. I did not use this query.

INSTRUCTIONS AND COMMENTS

Issues Experienced:

• 
  Inclusion of PCS codes from UMLS file of ICD-9-CM descriptions (first remove PCS, then remove period)
  
  • 
    Any ICD files from the UMLS will include both procedure (PCS) and diagnosis (CM) codes. The Procedure codes need to be removed first. Remove the Procedure codes before removing the periods. The format of procedure codes is XX.XX. There are ALWAYS two characters before the period. The format of diagnosis codes is XXX.XXXX. There are ALWAYS three characters before the period.
    
• 
  Period removal
  
  • 
    All periods need to be removed from the codes before they are linked within Access. The format of Diagnosis codes is XXX.XXXX. There are ALWAYS three characters before the period. The number of characters after the period can be zero or four. I recommend loading everything into Access first and then using the “Find” and “Replace” options available to remove the periods. This can also be done in Excel if the file is first an Excel file.
    
• 
  Codes and Descriptions not importing correctly
  
  • 
    When I first imported the ICD-10-CM descriptions directly from the text files available on the CMS website, the full descriptions did not import. Import the text file into an Excel document first. Make sure that all codes and descriptions import correctly. Then import the Excel file into Access. When importing, make sure that no additional columns are added, etc. and ensure that all codes and descriptions properly imported before linking it with other tables.
    

Access Instructions:

1. 
   Pull the new versions of the files discussed above into Access.
   
   1. 
      Forward and Backward mapping GEMs files
      
   2. 
      ICD-9-CM and ICD-10-CM Description files
      
   3. 
      MedlinePlus Connect Health Topic mapping file
      
2. 
   Check the imported data for each file. Ensure that all codes, descriptions, etc. imported correctly before linking the various tables.
   
3. 
   Run a query to find duplicates in the MedlinePlus Connect file. Make sure no duplicates exist.
   
4. 
   Create two queries: one for the forward mapping GEMs file and one for the backward mapping GEMs file. Results should show the ICD-9-CM codes (or 10CM), the descriptions of the ICD-9-CM codes, the associated ICD10 codes and their descriptions, MedlinePlus topics associated with each ICD-9-CM code, and the GEMs flags (see images and SQL view for relationship instructions. Also look at old Access to determine relationships):
   
   1. 
      Tables involved in forward mapping query (9 to 10 query):
      
      1. 
         9cm to 10cm gem
         
      2. 
         ICD10CM Descriptions
         
      3. 
         ICD9CM Descriptions UMLS
         
      4. 
         MedlinePlus Connect File (May11,2011 –ICD9 to Health Topics)
         
   2. 
      Tables related in backward mapping query (10 to 9 query):
      
      1. 
         10cm to 9cm gem
         
      2. 
         ICD10CM Descriptions
         
      3. 
         ICD9CM Descriptions UMLS
         
      4. 
         MedlinePlus Connect File (May11,2011 –ICD9 to Health Topics)
         
5. 
   Check the data for errors at this point as well (fields that didn’t populate, etc.). Ensure that all fields imported correctly. If a field did not import correctly, make sure that the original file has all periods and punctuation removed.
   
6. 
   Import the final versions into Excel. This will result in 2 Excel files.
   
7. 
   Filter out any code pairs that do not include a mapping to a Health Topic. This will be done in both Excel files.
   
8. 
   Ensure that each file is organized identically (same column order and headings)
   
9. 
   Combine the two files into one. This file will contain all GEMs code pairs with mappings to MedlinePlus Health Topics.
   
10. 
    If desired, you can run a query to find duplicates (instances where the ICD-9-CM code, ICD-10-CM code, and all Health Topics are the same) and pull these to review separately. I was unable to remove these duplicates and it is not a necessary step.
    

Access Screenshots and SQL View for 9 to 10 query and 10 to 9 query

1. 
   9-10 query
   

1. 
   Join properties for all tables EXCEPT ‘9 to 10cm gems’ should select option 3: Include ALL records from ‘9cm to 10cm gem’ and only those records from ‘ICD10CM Descriptions’ where the joined fields are equal’
   

Design View



SQL View:

SELECT [9cm to 10cm gem].ICD9, [9cm to 10cm gem].ICD10, [9cm to 10cm gem].Flags, [9cm to 10cm gem].[Perfect Flag], [9cm to 10cm gem].[Approximate Flag], [9cm to 10cm gem].[No Map Flag], [9cm to 10cm gem].[Combination Flag], [9cm to 10cm gem].[Scenario Flag], [9cm to 10cm gem].[Choice List Flag], [May11, 2011 - ICD9 to M+ Topics].[ICD9Description-M+], [May11, 2011 - ICD9 to M+ Topics].[Mplus Topic 1], [May11, 2011 - ICD9 to M+ Topics].[Mplus Topic 2], [May11, 2011 - ICD9 to M+ Topics].[Mplus Topic 3], [icd9cm 2010ab umls - May 13].[ICD9UMLS2010AB Description], [ICD10CM Descriptions May 13 2011].ICD10Description

FROM [ICD10CM Descriptions May 13 2011]

RIGHT JOIN (([May11, 2011 - ICD9 to M+ Topics]

RIGHT JOIN [9cm to 10cm gem]

ON [May11, 2011 - ICD9 to M+ Topics].[ICD9 Code] = [9cm to 10cm gem].ICD9)

LEFT JOIN [icd9cm 2010ab umls - May 13]

ON [9cm to 10cm gem].ICD9 = [icd9cm 2010ab umls - May 13].ICD9CodeUMLS2010AB)

ON [ICD10CM Descriptions May 13 2011].ICD10Code = [9cm to 10cm gem].ICD10;

2. 
   10 – 9 Query
   

1. 
   Join properties for all tables EXCEPT ‘10cm to 9cm gems’ should select option 3: Include ALL records from ‘9cm to 10cm gem’ and only those records from ‘ICD10CM Descriptions’ where the joined fields are equal’
   

Design View



SQL View

SELECT [10cm to 9cm gem].ICD9, [10cm to 9cm gem].[Perfect Flag], [10cm to 9cm gem].[Approximate Flag], [10cm to 9cm gem].[No Map Flag], [10cm to 9cm gem].[Combination Flag], [10cm to 9cm gem].[Scenario Flag], [10cm to 9cm gem].[Choice List Flag], [ICD10CM Descriptions May 13 2011].ICD10Description, [10cm to 9cm gem].ICD10, [May11, 2011 - ICD9 to M+ Topics].[ICD9Description-M+], [May11, 2011 - ICD9 to M+ Topics].[Mplus Topic 1], [May11, 2011 - ICD9 to M+ Topics].[Mplus Topic 2], [May11, 2011 - ICD9 to M+ Topics].[Mplus Topic 3], [10cm to 9cm gem].Flags, [icd9cm 2010ab umls - May 13].[ICD9UMLS2010AB Description]

FROM [icd9cm 2010ab umls - May 13]

RIGHT JOIN (([May11, 2011 - ICD9 to M+ Topics]

RIGHT JOIN [10cm to 9cm gem] ON [May11, 2011 - ICD9 to M+ Topics].[ICD9 Code] = [10cm to 9cm gem].ICD9)

LEFT JOIN [ICD10CM Descriptions May 13 2011] ON [10cm to 9cm gem].ICD10 = [ICD10CM Descriptions May 13 2011].ICD10Code) ON [icd9cm 2010ab umls - May 13].ICD9CodeUMLS2010AB = [10cm to 9cm gem].ICD9;

APPENDIX G: PRESENTATION SCREENSHOTS

1^ The MedlinePlus Connect team supports ICD-9-CM and the SNOMED CT CORE Problem List Subset primarily because the Centers for Medicare and Medicaid (CMS) requires providers to “maintain an up-to-date problem list of current and active diagnoses based on ICD-9-CM or SNOMED CT” [3, p. 44336].

2^The MedlinePlus Health Topic XML is available here: http://www.nlm.nih.gov/medlineplus/xml.html

3^UMLS source information for MedlinePlus Connect (release 2011AA) is available here: http://www.nlm.nih.gov/research/umls/sourcereleasedocs/current/MEDLINEPLUS/

4^ A description of the Restrict to MeSH Algorithm is available on the NLM’s Indexing Initiative page at http://ii.nlm.nih.gov/MTI/RTM.shtml. In addition, the following citation provides additional information about the development and use of the algorithm: Bodenreider O, Nelson SJ, Hole WT, and Chang HF. Beyond synonymy: exploiting the UMLS semantics in mapping vocabularies. Proc AMIA Symp. 1998. 815-819. PMC2232139.

5^ The HHS Final Rule was published on January 16, 2009 (45 CFR Part 162)

6^ CDC website for ICD-10-CM and its 2011 release http://www.cdc.gov/nchs/icd/icd10cm.htm#10update

7^ https://www.cms.gov/ICD10/11b1_2011_ICD10CM_and_GEMs.asp

8^ https://www.cms.gov/ICD10/11b_2011_ICD10PCS.asp

9^ The Associate Fellow used the Raosoft web tool (http://www.raosoft.com/samplesize.html) to calculate the sample size and StatTrek’s random number generator⁽http://stattrek.com/Tables/Random.aspx) to determine the randomly selected sample.

10^ A total of 4979 SXX codes and 1280 TXX codes, for a total of 6259 codes are found in these complete sections of the ICD-9-CM to ICD-10-CM GEMs file.