OHDSI Home | Forums | Wiki | Github

[Vocab] NDCs - Multi RxNorm Mappings

Vocabulary Users
#1

@Christian_Reich,

In the v5.0 10-NOV-15, I’m seeing some NDCs that map to multiple RxNorms that I don’t think should. I see two things going on:

  • The “Maps To” relationship is picking up a clinical drug and a branded drug. Following this map I would look like the person was getting two records for this drug.
  • Sometimes instead of picking one dose form it is selecting them all.

Example 1
44941228-6 ACTUAT Interferon Alfa-2b 5000000 UNT/ACTUAT Prefilled Syringe [Intron A] maps to both:

  • 1380095-Interferon Alfa-2b 25000000 UNT/ML Injectable Solution [Intron A]
  • 19078663-Interferon Alfa-2b 25000000 UNT/ML Injectable Solution.

Example 2
I’m seeing a couple of these where it is picking up different dose forms.

45119121-1.5 ML Somatropin 5.83 MG/ML Prefilled Syringe [Saizen] to the following:

  • 1585062-Somatropin 4.4 MG/ML Injectable Solution
  • 1585206-Somatropin 4.4 MG/ML Injectable Solution [Saizen]
  • 19127406-Somatropin 5.83 MG/ML Injectable Solution
  • 19127408-Somatropin 5.83 MG/ML Injectable Solution [Saizen]

Here is the code I’m running to find these guys:

WITH CTE_VOCAB_MAP AS (
       SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.concept_name AS SOURCE_CODE_DESCRIPTION, c.vocabulary_id AS SOURCE_VOCABULARY_ID, 
                           c.domain_id AS SOURCE_DOMAIN_ID, c.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, c.valid_start_date AS SOURCE_VALID_START_DATE, 
						   c.valid_end_date as SOURCE_VALID_END_DATE,c.INVALID_REASON AS SOURCE_INVALID_REASON,
                           c1.concept_id AS TARGET_CONCEPT_ID, c1.concept_name AS TARGET_CONCEPT_NAME, c1.VOCABULARY_ID AS TARGET_VOCABUALRY_ID, c1.domain_id AS TARGET_DOMAIN_ID, c1.concept_class_id AS TARGET_CONCEPT_CLASS_ID, 
                           c1.INVALID_REASON AS TARGET_INVALID_REASON, c1.standard_concept AS TARGET_STANDARD_CONCEPT
       FROM CONCEPT C
             JOIN CONCEPT_RELATIONSHIP CR
                        ON C.CONCEPT_ID = CR.CONCEPT_ID_1
                        AND CR.invalid_reason IS NULL
                        AND cr.relationship_id = 'Maps To'
              JOIN CONCEPT C1
                        ON CR.CONCEPT_ID_2 = C1.CONCEPT_ID
                        AND C1.INVALID_REASON IS NULL
       UNION
       SELECT source_code, SOURCE_CONCEPT_ID, SOURCE_CODE_DESCRIPTION, source_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, 
			c1.VALID_START_DATE AS SOURVE_VALID_START_DATE, c1.valid_end_date AS SOURCE_VALID_END_DATE,			
                     stcm.INVALID_REASON AS SOURCE_INVALID_REASON,target_concept_id, c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, target_vocabulary_id, c2.domain_id AS TARGET_DOMAIN_ID, c2.concept_class_id AS TARGET_CONCEPT_CLASS_ID, 
                     c2.INVALID_REASON AS TARGET_INVALID_REASON, c2.standard_concept AS TARGET_STANDARD_CONCEPT
       FROM source_to_concept_map stcm
              LEFT OUTER JOIN CONCEPT c1
                     ON c1.concept_id = stcm.source_concept_id
              LEFT OUTER JOIN CONCEPT c2
                     ON c2.CONCEPT_ID = stcm.target_concept_id
       WHERE stcm.INVALID_REASON IS NULL
)
SELECT SOURCE_CODE, COUNT(DISTINCT TARGET_CONCEPT_ID)
FROM CTE_VOCAB_MAP
WHERE SOURCE_VOCABULARY_ID IN ('NDC')
AND TARGET_VOCABUALRY_ID IN ('RxNORM')
AND SOURCE_CODE_DESCRIPTION NOT LIKE '%{%' /*filter to attempt to get rid of legit multi mappings*/
AND SOURCE_CODE_DESCRIPTION NOT LIKE '%,%' /*filter to attempt to get rid of legit multi mappings*/
GROUP BY SOURCE_CODE
HAVING COUNT(DISTINCT TARGET_CONCEPT_ID) > 1
t