OHDSI Home | Forums | Wiki | Github

Searching ATC to standard concept id(RxNorm) and relationship

Vocabulary Users
#1

Dear sir,

I’m defining code set for Diabetes Drugs by using ATC. I’m facing difficulties finding the right concept id through direct coding using the SQL query tool from DB. I could find it in Athena, ATC to RxNorm ingredient(from the relationship Table) but there is no such thing in DB. I only found ATC-Rxnorm but the description of the content was a bit different and it makes me confused.

in case A10BD01,
Athena: |40030439|chlorpropamide / phenformin Oral Tablet|ATC to RxNorm|RxNorm|
|40046145|glyburide / phenformin Oral Tablet|ATC to RxNorm|RxNorm|

DB:
SELECT * FROM concept WHERE concept_code = ‘A10BD01’
SELECT * FROM concept_relationship WHERE concept_id_1 = 21600767 and relationship_id = ‘Map to’
No results.

  • I downloaded vocabulary from Athena and loaded Psql server, CDM V6.
    Please guide me in right direction?
    thank you
#2

how about this query.
SELECT * FROM concept_relationship WHERE concept_id_1 = 21600767 and relationship_id = ‘ATC - RxNorm’;

1 Like
#3

To get the list of RxNorm drugs please use concept_ancestor:
select * from concept_ancestor join concept on descendant_concept_id = concept_id where ancestor_concept_id=21600767;

You can check out this post.

What do you mean by ICD-10 - RxNorm relationships? These are two separate vocabs serving different domains with no relationships between them.

1 Like
#4

than you.
i tried but there is no result… appreciate your attention.

#5

thank you for your help
i will read your post.

appreciate your attention :slight_smile:

  • in regards of ICD I got confused with KCD sorry for misconception
#6

Dear Anna,
I have a question that fits well in this thread.
I was looking at developing a standard concept set based on well defined ATC5 codes and I tested two approaches.

  1. Your recommended way of going directly for all descendants
select descendant_concept_id  as standard_concept_id
  from concept_ancestor as ca 
 inner join concept as c1 
    on ca.ancestor_concept_id = c1.concept_id
 inner join concept as c2
    on ca.descendant_concept_id = c2.concept_id
 where c1.concept_code in ('ATC1' 'ATC2') 
   and c2.standard_concept = 'S';
  1. Going through the concept relationship to find the ingredient first
select descendant_concept_id  as standard_concept_id
  from concept as c1
 inner join concept_relationship as cr
    on c1.concept_id = cr.concept_id_1
 inner join concept_ancestor as ca
    on cr.concept_id_2 = ca.ancestor_concept_id
 inner join concept as c2
    on ca.descendant_concept_id = c2.concept_id
 where c1.concept_code in ('ATC1' 'ATC2') 
   and cr.relationship_id = 'Maps to' --  'ATC - RxNorm pr lat' gives the same result in my case
   and c2.standard_concept = 'S';

The two approaches give different result, with number 2) identifying more standard concepts - I have also inspected them visually and they do seem to mapped correctly.

Do you have suggestions why that could be? Is the second approach the way to go after all?
Would love to hear your thoughts on this :slight_smile: !

#7

Hey, could you please provide your ATC codes so that I can look at them?

#8

An example could be these two:
N06DA01, N06DX01, but I am generally interested in all the codes starting with ‘N06D’ - I believe that 5 ingredients of this category are mapped to standard vocabularies (N06DA01, N06DA02, N06DA03, N06DA04 and N06DX01).
And big thanks for taking the time to look at this!

t