OHDSI Home | Forums | Wiki | Github

Building Drug Hierarchies

Hello all, I am newer to the community and am in need of some guidance!

I am trying to build a list of RxNorm drugs with hierarchical details. I read this thread: https://forums.ohdsi.org/t/concept-hierarchies AND ATC release - Vocabulary Users - OHDSI Forums as it’s similar to what I am trying to accomplish but in table form.

Currently I have RxNorm and Extension drugs as descendants with ATCs mapping to them as ancestors, so that I have all existing ATC information (and codes) per RxNorm drug. My hope is to have some time of hierarchical information per concept, but I am not as of now.

My goal is to have something like this for each RxNorm concept:
codeine phosphate 2 MG/ML : NERVOUS SYSTEM | ANALGESICS | OPIOIDS | Natural opium alkaloids | codeine, combinations excl. psycholeptics; systemic

Is this a solid way to make a hierarchy in the drug domain? Is it even possible with ATC to RxNorm relationships? Does anyone have tips, other articles to read, or advice?

Thank you in advance!

Hi @rosemarybliss:

Welcome to the family.

What do you mean by that? A clinical drug? That would be the combination of ingredient(s), strength(s) and dose form. Your “codeine phosphate 2 MG/ML” lacks the dose form, which means it is a clinical drug component. I wouldn’t use that for building a hierarchy to ATC, as that may be ambiguous. One of the clinical drugs with that concentration of codeine would be codeine phosphate 2 MG/ML Oral Solution.

You have that information in the CONCEPT_ANCESTOR table already. You just need to select properly:

select an.concept_name as atc_name, de.concept_name as rx_name
from concept an
join concept_ancestor a on a.ancestor_concept_id=an.concept_id
join concept de on de.concept_id=a.descendant_concept_id
where de.concept_id=40223090 -- codeine phosphate 2 MG/ML Oral Solution
and an.vocabulary_id='ATC';

And codeine phosphate solution is not in “combinations, excl. psycheleptics”. Combinations are by definition drugs with more than just one ingredient. (You may be looking for combinations containing codeine, that would be possible as well, but another query.) (You may also want to concatenate the ATC names ordered by their concept_class_id. How to do that depends on your SQL dialect.)

Good luck.

Thanks for your quick reply! I appreciate your help.

I see, so would you say “clinical drug” is the only concept class ID that could accurately build a hierarchy using ATC?

My view currently has all RxNorm and Extension drugs in the drug domain. I liked the idea of using ATCs as it’s an intelligent key that I can break down into 5 hierarchical elements/ categories. For instance, I would like to add a column to identify what system(s) the drug targets, like all drugs acting on the nervous system (ATCs starting with N) as nervous system. Is this not possible/ accurate with ATC?