OHDSI Home | Forums | Wiki | Github

RxNorm Ingredient to Clinical Drug Form (how to exclude multi-ingredient drugs?)

Vocabulary Users
#1

I know the Ingredient. I’m trying to use the concept_relationship table to find all Clinical Drug Form and Clinical Dose Group values for that Ingredient, but I only want the ones that contain just that Ingredient.

Example:
Ingredient: rituximab
Include: Clinical Drug Form: rituximab Injection
I do NOT want to include Clinical Drug Form: Hyaluronidase / rituximab Injection

I can use ‘RxNorm ing of’, but it provides both results. I’m guessing that I need to use RxNorm directly, but I wanted to confirm here first that it’s not workable with just the concept_relationship table.

Many thanks!

#2

Hi Gillian,

Would it work to group by the clinical drug form to count the number of ingredients for each form? Then you can select out the single ingredient ones and join it against a list of drug forms containing rituximab?

Hope that helps,
Darwin

select
c1.concept_name as drug_name
from v_concept as c1
inner join v_concept_relationship as cr1
on c1.concept_id = cr1.concept_id_1
inner join v_concept as c2
on c2.concept_id = cr1.concept_id_2
and lower(c2.concept_name) = ‘rituximab’
inner join
(select
c1.concept_name as drug_name,
COUNT(distinct c2.concept_name) AS ingreds
from v_concept as c1
inner join v_concept_relationship as cr1
on c1.concept_id = cr1.concept_id_1
inner join v_concept as c2
on c2.concept_id = cr1.concept_id_2
where c1.vocabulary_id = ‘RxNorm’ and c1.concept_class_id = ‘Clinical Drug Form’ and cr1.relationship_id = ‘RxNorm has ing’ and c2.concept_class_id = ‘Ingredient’
group by drug_name) as subQuery
on c1.concept_name = subQuery.drug_name
and subQuery.ingreds = 1
where c1.vocabulary_id = ‘RxNorm’ and c1.concept_class_id = ‘Clinical Drug Form’ and cr1.relationship_id = ‘RxNorm has ing’ and c2.concept_class_id = ‘Ingredient’

#3

Darwin gave a good solution.

  1. The same can be done through drug_strength table, which also will only have one row with one ingredient.
  2. Another option w/o using SQL is simply go to Athena and limit the search to Clinical Drug Form and then get all descendants of those Clinical Drug Forms: https://athena.ohdsi.org/search-terms/terms?domain=Drug&standardConcept=Standard&conceptClass=Clinical+Drug+Form&page=1&pageSize=15&query=rituximab
  3. Finally, querying descendants of mono-ingredient ATC will give you only mono-ingredient drugs:
    select * from concept
    join concept_ancestor on concept_id = descendant_concept_id
    where ancestor_concept_id=21603756; – ATC rituximab
#4

Hi, Darwin! :wave:

Thank you, this is great.

t