OHDSI Home | Forums | Wiki | Github

Connecting drug_concept_ids to atc3 classes

Vocabulary Users
#1

I’m trying to make a lookup table from drug exposure drug_concept_ids to atc3 classes. I understand some drugs will have more than one atc3 class, but I don’t think I’m doing this quite right. For instance, I got one drug (Acetaminophen 250 MG / Caffeine 30 MG / Chlorpheniramine 2 MG / Hydrocodone Bitartrate 5 MG / Phenylephrine 10 MG Oral Tablet [Hycomine Compound]) that came back with 10 atc3 classes including AGENTS FOR TREATMENT OF HEMORRHOIDS AND ANAL FISSURES FOR TOPICAL USE and COUGH SUPPRESSANTS, EXCL. COMBINATIONS WITH EXPECTORANTS which seems both unlikely and kind of gross.

Here’s the query. The last join is just so I can limit to the list I made with drugs that have lots of atc3 classes. Otherwise I’m just trying to use concept_ancestor to link from standard drug_concept_ids to atc3 classes.

select  atc3s.atc3s,
	ccd.concept_id drug_concept_id, ccd.concept_name drug_concept_name, ccd.concept_class_id drug_class_id,
	atc.concept_id atc_concept_id, atc.concept_name atc_concept_name			
from concept atc
join concept_ancestor ca on atc.concept_id = ca.ancestor_concept_id
join concept ccd on ca.descendant_concept_id = ccd.concept_id 
			and ccd.standard_concept = 'S'
			and ccd.invalid_reason is null
join drug_ids_with_many_atc3s atc3s on ccd.concept_id = atc3s.drug_concept_id
where atc.concept_class_id = 'ATC 3rd'
order by 4,3,6

The drug I mentioned is apparently an oral tablet, so it’s not topical but may contain an ingredient that’s used in a topical drug and that’s how that class is being caught. Maybe this kind of issue is uncommon enough that I shouldn’t worry about it, but I just wanted to check if there’s some correct way to do this that I’m missing.

Thanks,
Sigfried

#2

Hi @sigfried_gold:

This is a good question, and useful to have the discussion.

A few things:

  1. RxNorm clinical drugs are mapped to ATC concepts. The RxNorm concept
    can contain multiple ingredients, in many cases these map up to their
    corresponding combination product, but at a higher level, they may have
    multiple parents.

  2. The specific example you provided is a Branded Drug Comp (
    http://www.ohdsi.org/web/atlas/#/concept/19068767), which is invalid,
    nonstandard concept, so shouldn’t appear in your CDM.

  3. Here’s a revised query to find ATC3 for standard drug concepts:

select drug.concept_id, drug.concept_name,
atc3.concept_id as atc3_concept_id, atc3.concept_name as atc3_concept_name
from
( select concept_id, concept_name
from concept
where standard_concept = ‘S’
and domain_id = ‘Drug’
and invalid_reason is null
) drug
inner join
concept_ancestor ca
on drug.concept_id = ca.descendant_concept_id
inner join
(
select concept_id, concept_name
from concept
where vocabulary_id = ‘ATC’
and concept_class_id = ‘ATC 3rd’
and invalid_reason is null
) atc3
on ca.ancestor_concept_id = atc3.concept_id

#3

Thanks so much, Patrick!

I’m repeating your version here with formatting to make it easier for folks to see what you’re doing.

select
	drug.concept_id as drug_concept_id,
	drug.concept_name as drug_concept_name,
	atc3.concept_id as atc3_concept_id, 
	atc3.concept_name as atc3_concept_name 
from (
	select concept_id, concept_name 
	from concept 
	where standard_concept = 'S' 
	  and domain_id = 'Drug' 
	  and invalid_reason is null 
) drug 
inner join concept_ancestor ca on drug.concept_id = ca.descendant_concept_id 
inner join (
	select concept_id, concept_name 
	from concept 
	where vocabulary_id = 'ATC' 
	  and concept_class_id = 'ATC 3rd' 
	  and invalid_reason is null 
)  atc3 on ca.ancestor_concept_id = atc3.concept_id
t