OHDSI Home | Forums | Wiki | Github

Mapping RxNorm standard concepts to ATC 4th level

Vocabulary Users
#1

@Christian_Reich @Dymshyts
Would it be safe to go directly from drug concepts in DRUG_EXPOSURE table to ATC 4th level through CONCEPT_ANCESTOR table? Do we have all the mappings there?

Select distinct A.DRUG_CONCEPT_ID as Drug_concept_id,
				C2.CONCEPT_ID as ATC4th_concept_id
FROM DRUG_EXPOSURE A
JOIN CONCEPT_ANCESTOR CA2
	ON CA2.descendant_CONCEPT_ID = A.DRUG_CONCEPT_ID
JOIN CONCEPT C2
	ON CA2.ancestor_CONCEPT_ID = C2.concept_id
	and c2.vocabulary_id = 'ATC'
	and c2.concept_class_id = 'ATC 4th'
	and c2.invalid_reason is null;
Starting ATC Mapping Working Group
#2

@Christian_Reich @Dymshyts Any thoughts about this?

#3

@abedtash_hamed:

@Dymshyts is going to answer in detail.

Short answer: You can, but there is an issue being worked on, which is why we are a little bashful. In some cases, there are two or more ATC5 concepts that are mapped to one equivalent RxNorm Ingredient. They all look like ingredients, and their concept_name is just the name of the ingredient. But what happens in reality that they are ambiguous. The antibiotic of an ophthalmic preparation is a different ATC5 code than the same compound than that of a systemic ATC5. In RxNorm, you don’t have “systemic Ingredients” and “ophthalmic Ingredients”. In order to avoid having wrong hierarchies (the ophthalmic antibiotic becoming an ancestor to all systemic RxNorm products) we took these links out. We are currently working on putting them back in, but with a manual cleansing. Sorry, I have no real-life example.

Bottom line: Use it, but we have a few missing ATC to RxNorm hierarchy links.

#4

Thanks @Christian_Reich! I know exactly what you are talking about. We fixed this issue few months ago internally to a great extent, but keeping it up-to-date is the issue. Let me know if you needed any help from my side.

#5

@abedtash_hamed:

Oh!!! YES. How did you go about it?

#6

We took into account several aspects of the drug concept: ingredient name, dose form, therapeutic domain, even DDD (eg, Finasteride 1 mg is D11AX10 but 5 mg is G04CB01).

#7

@Christian_Reich @abedtash_hamed
Hi guys, I’m new to all of this. Can you please tell me where you get the tables you are referencing in your sql?

I have downloaded data from here, but the data (i.e. all the .rrf files) do not have the same names you are using to refer to the tables. For example, there is no table called concept.

Essentially, I have RXNORM and SNOMED_US codes and want to map the relevant concepts to ATC codes (up to level 5). It appears possible, but I need the tables you guys are referring to.

Thanks a lot!
Alastair

#8

HI @Alastair_Morris,

The general idea of the OHDSI vocabulary is described here:

so we convert the source data (i.e. these .rrf files) to our format.
The tables can be taken from here:
http://athena.ohdsi.org/vocabulary/list

#9

Hi

Silly question - where can I find the Table - DRUG_EXPOSURE ??
I see the concept Tables but not - DRUG_EXPOSURE

Thanks
Steve

#10

@samoc:

DRUG_EXPOSURE is part of the OMOP CDM. It’s where the data are. The CDM is described here: https://github.com/OHDSI/CommonDataModel/wiki. The vocabulary tables are for referencing and querying the data.

What are you trying to do?

#11

Hi
Was trying to get a listing of NDC mapped to ATC . I found one of your queries. The first problem I found and solved was vocabulary_id=‘ATC’
The original download - I didn’t specify ATC. Now I specify NDC,RXNORM and ATC and now this query returns nothing. Is there some other vocabulary I need to include ?

Thanks

select
atc.concept_id as atc_id, atc.concept_name as atc_name, atc.concept_code as atc_code, atc.concept_class_id as atc_class,
ndc.concept_id as atc_id, ndc.concept_name as ndc_name, ndc.concept_code as ndc_code, ndc.concept_class_id as ndc_class
from concept atc
join concept_ancestor a on a.ancestor_concept_id=atc.concept_id
join concept_relationship r on r.concept_id_1=a.descendant_concept_id and r.invalid_reason is null and r.relationship_id=‘Mapped from’
join concept ndc on ndc.concept_id=concept_id_2 and ndc.vocabulary_id=‘NDC’
where atc.vocabulary_id=‘ATC’

#12

Well,

is enough to get the results.

Maybe you have some problems with vocabulary load, can you share the script used for upload the tables to server?

#13

Hi

Here is the process
1)Checked NDC,RXNORM,ATC - for download
2)Imported Concept,Concept_Ancestor,Concept_Relationship into local Sqlserver just using the import wizard with no error
3)Started to breakdown query into parts - If I run thru with the 1st join then domain_id = Drug records are present, If I add the 2nd Join - nothing is returned.

Seems all domain_id = ‘Drug’ and vocabulary= ‘ATC’ have relationship = ‘Is a’

I assume my download is still not pulling correct content ?

Thanks

SELECT *
from concept atc
join concept_ancestor a on a.ancestor_concept_id=atc.concept_id
join concept_relationship r on r.concept_id_1=a.descendant_concept_id and r.relationship_id=‘Mapped from’
where atc.vocabulary_id=‘ATC’

#14

Hi

Thanks for all your help - I figured it out
Was missing one vocabulary - RxNorm Extension

Thanks again

#15

Interesting,
so you added to your package RxNorm Extension vocabulary and the query

select
atc.concept_id as atc_id, atc.concept_name as atc_name, atc.concept_code as atc_code, atc.concept_class_id as atc_class,
ndc.concept_id as atc_id, ndc.concept_name as ndc_name, ndc.concept_code as ndc_code, ndc.concept_class_id as ndc_class
from concept atc
join concept_ancestor a on a.ancestor_concept_id=atc.concept_id
join concept_relationship r on r.concept_id_1=a.descendant_concept_id and r.invalid_reason is null and r.relationship_id=‘Mapped from’
join concept ndc on ndc.concept_id=concept_id_2 and ndc.vocabulary_id=‘NDC’
where atc.vocabulary_id=‘ATC’

started working?

I’m wondering, because it looks like the query doesn’t affect the RxNorm Extension;
you take the ATC, then find its descendants - ATCs with lower level and RxNorm concepts
then use concept_relationships to find NDCs Mapped to RxNorm.
Logically if ATC goes to RxNorm Extension, it doesn’t have RxNorm in this hierarchical branch anyway.
So there’s now RxNorm Extension in this logical chain.

@aostropolets, thoughts?

#16

@samoc, @Dymshyts
RxE is obviously not the case.
Something must be wrong in the previous steps (might be in step 2).
For instance, RxNorm 702961 Meprobamate 200 MG / tridihexethyl 25 MG Oral Tablet has ATC ancestor 21600001 (ALIMENTARY TRACT AND METABOLISM) and relates to a bunch of ndc drugs (like 44882095 Meprobamate 200 MG / tridihexethyl 25 MG Oral Tablet) .
Going by the fact that I can get these results on our server, we need to go step-by-step through your process.

  1. First of all, please check if you have all these concepts in your concept table:
    702961
    21600001
    44882095
  2. Then check if you have this relationship:
    select * from concept_relationship where concept_id_1=702961 and concept_id_2=44882095;
  3. And, finally,
    select * from concept_ancestor where descendant_concept_id=702961 and ancestor_concept_id=21600001 ;
#17

Hi, I just want to get back on the original topic. I’m wondering whether the original issue has been solved? about the ambiguity of ATC5 to RxNorm? What was the final method that was implemented if it has been resolved?

was @abedtash_hamed’s method used (taking into account more attributes such as the DDD)?

We took into account several aspects of the drug concept: ingredient name, dose form, therapeutic domain, even DDD (eg, Finasteride 1 mg is D11AX10 but 5 mg is G04CB01).

#18

It’s in progress in ATC Mapping Working Group. We meet biweekly to discuss the progress and discuss challenges. You are welcome to join. The next meeting is tomorrow 6/29. The forum page is here: ATC Working Group: Meeting Agenda and Minutes

t