OHDSI Home | Forums | Wiki | Github

Why would a source code be in both RxNorm and RxNorm Extension


(Don Torok) #1

I see 256 source codes that have both RxNorm and RxNorm Extension mappings where the target concept id are different. If ETL looks for drug mapping where target domain is ‘Drug’ it will end up double counting these drugs. What is expected of ETL when mapping drugs? Should RxNorm Extention be excluded when the data is from a U.S.A source?

Vocabulary version v5.0 20-AUG-18


(Anna Ostropolets) #2

Don, could you be so kind to share the file with these concepts?


(Don Torok) #3

Not sure what is going on with Gemscript, but I noticed problem with HCPCS codes when QCing an ETL.

SELECT RxNorm.source_vocabulary_id, rxNorm.source_code, RxNorm.target_concept_id, RxNorm.target_concept_name, RxNorm.target_vocabulary_id
, ext.target_concept_id,ext.target_concept_name, ext.target_vocabulary_id
FROM
(
SELECT c1.vocabulary_id as source_vocabulary_id, c1.concept_code as source_code, c1.concept_id as source_concept_id
, c2.concept_id as target_concept_id,c2.concept_name as target_concept_name, c2.vocabulary_id as target_vocabulary_id
FROM concept c1
JOIN concept_relationship ON concept_id_1 = c1.concept_id
JOIN concept c2 ON c2.concept_id = concept_id_2
WHERE c1.vocabulary_id IN( ‘NDC’, ‘HCPCS’, ‘Gemscript’) AND relationship_id = ‘Maps to’ AND c2.vocabulary_id = ‘RxNorm’
) RxNorm
JOIN
(
SELECT c1.vocabulary_id as source_vocabulary_id, c1.concept_code as source_code, c1.concept_id as source_concept_id
, c2.concept_id as target_concept_id,c2.concept_name as target_concept_name, c2.vocabulary_id as target_vocabulary_id
FROM concept c1
JOIN concept_relationship ON concept_id_1 = c1.concept_id
JOIN concept c2 ON c2.concept_id = concept_id_2
WHERE c1.vocabulary_id IN( ‘NDC’, ‘HCPCS’, ‘Gemscript’) AND relationship_id = ‘Maps to’ AND c2.vocabulary_id = ‘RxNorm Extension’
) ext
ON ext.source_vocabulary_id = RxNorm.source_vocabulary_id AND ext.source_code = rxNorm.source_code


(Anna Ostropolets) #4

interesting. If you exclude RxNorm Extension, then you might not get other valid mappings. In this way, I’d better exclude it for this batch only. Besides, seeing multiple Clinical/Branded Drug Components or Ingredients is totally fine.


(Don Torok) #5

Any update about correcting HCPCS codes that map to both RxNorm and RxNorm Extensions?


(Dmytry Dymshyts) #6

Hi @DTorok,

Already fixed internally.
Not released yet, as we want to improve relationships for procedures also. And then release.
Do you need it urgently?


(Don Torok) #7

Thanks, not urgent, just wanted a time line.


(Don Torok) #8

I ran the query at the top of this topic against vocabulary version v5.0 05-OCT-18 and source codes mapped to both RxNorm and RxNorm Extensions continues to be a problem. 160 Gemscript and 89 HPCPS code map to both RxNorm and RxNorm Extensions. This will result in double counting these drug records.


(Don Torok) #9

I refined query to identify ‘duplicate’ mappings into RxNorm and RxNorm Extensions. Query finds a source code that maps to both RxNorm and RxNorm Extension and then checks the ingredient of the mapped to standard concept. If the ingredients are the same, it is considered a duplicate. Running against v5.0 05-OCT-18 I get 25 GemScript and 89 HPCPS codes that appear to be duplicates.

SELECT *
FROM
(
SELECT source_vocabulary_id, source_code, source_code_description
, r_target_concept, r_target_description
, e_target_concept, e_target_description
, rac.concept_name as r_ingredient
, eac.concept_name as e_ingredient
FROM
(
SELECT r.source_vocabulary_id, r.source_code, r.source_code_description
, r.target_concept_id as r_target_concept, r.target_concept_name AS r_target_description
, e.target_concept_id as e_target_concept, e.target_concept_name AS e_target_description
FROM
( SELECT c1.vocabulary_id AS source_vocabulary_id, c1.concept_code as source_code, c1.concept_name as source_code_description
, c2.concept_id as target_concept_id, c2.concept_name AS target_concept_name
FROM concept c1
JOIN concept_relationship ON concept_id_1 = c1.concept_id
JOIN concept c2 ON c2.concept_id = concept_id_2
WHERE relationship_id = ‘Maps to’ AND c2.vocabulary_id = ‘RxNorm’ – and c1.concept_code = ‘J1720’
) r
JOIN
(SELECT c1.vocabulary_id AS source_vocabulary_id, c1.concept_code as source_code, c1.concept_name as source_code_description
, c2.concept_id as target_concept_id, c2.concept_name AS target_concept_name
FROM concept c1
JOIN concept_relationship ON concept_id_1 = c1.concept_id
JOIN concept c2 ON c2.concept_id = concept_id_2
WHERE relationship_id = ‘Maps to’ AND c2.vocabulary_id = ‘RxNorm Extension’ – and c1.concept_code = ‘J1720’
) e on e.source_code = r.source_code and e.source_vocabulary_id= r.source_vocabulary_id
)
JOIN concept_ancestor ra ON ra.descendant_concept_id = r_target_concept
JOIN concept rac ON rac.concept_class_id = ‘Ingredient’ AND rac.concept_id = ra.ancestor_concept_id
JOIN concept_ancestor ea ON ea.descendant_concept_id = e_target_concept
JOIN concept eac ON eac.concept_class_id = ‘Ingredient’ AND eac.concept_id = ea.ancestor_concept_id
) WHERE r_ingredient = e_ingredient


(Anna Ostropolets) #10

Right, these exist. But thanks for pointing it out again. Will be fixed


t