OHDSI Home | Forums | Wiki | Github

ATC release

Vocabulary Users
#21

This was very informative thank you!

I am currently creating a crosswalk between RxNorm drugs and ATCs. However, I am only getting 5% RxNorm drugs mapping to any ATC. Is this correct or is there something wrong?

I am using the CONCEPT_RELATIONSHIP table to look for those mappings, including all relationship IDs.

#22

You could use concept_relationship or concept_ancestor depending on your use case. ~80% of RxNorm drugs are linked (we published a paper that is yet to be released describing the process and numbers).

Would you mind providing the query you’ve used?

#23

Of course! This is from a larger query, so excuse the noise. I listed all RxNorm drugs and any ATCs that had a relationship to them. As of now, I’m getting about a 5% mapping rate. My goal in using ATCs is to form a hierarchy of sorts, for instance all drugs that serve the nervous system have a flag that indicates that.

WITH ATC_CODE AS
(
SELECT
atc_cr.SRC_CONCEPT_ID_1 AS SRC_ATC_CONCEPT_ID
, atc_con.SRC_CONCEPT_NAME AS SRC_ATC_CONCEPT_NAME
, atc_cr.SRC_RELATIONSHIP_ID AS SRC_RELATIONSHIP_ID
, atc_con.SRC_CONCEPT_CODE AS SRC_ATC_CODE
, atc_cr.SRC_CONCEPT_ID_2 AS SRC_MAPPED_CONCEPT_ID
, atc_con2.SRC_CONCEPT_NAME AS SRC_MAPPED_CONCEPT_NAME
, atc_con2.SRC_CONCEPT_CODE AS SRC_MAPPED_CONCEPT_CODE
, atc_con.SRC_CONCEPT_CLASS_ID
–Aggregates all ATCs that map to an RxNorm concept
, LISTAGG(“SRC_ATC_CODE”, ', ') WITHIN GROUP (ORDER BY “SRC_ATC_CODE”)
OVER (PARTITION BY “SRC_MAPPED_CONCEPT_ID”, “SRC_MAPPED_CONCEPT_NAME”) AS SRC_ALL_ATCs
–Checks for any cartesians
, ROW_NUMBER() OVER (PARTITION BY SRC_MAPPED_CONCEPT_ID
ORDER BY SRC_MAPPED_CONCEPT_ID, SRC_MAPPED_CONCEPT_ID) AS ROW_NUMBER_COUNTER_ATC
FROM
CONCEPT_RELATIONSHIP atc_cr
LEFT OUTER JOIN CONCEPT atc_con
ON atc_con.SRC_CONCEPT_ID = atc_cr.SRC_CONCEPT_ID_1
LEFT OUTER JOIN
CONCEPT atc_con2
ON atc_con2.SRC_CONCEPT_ID = atc_cr.SRC_CONCEPT_ID_2
WHERE atc_con.SRC_DOMAIN_ID = ‘Drug’
AND atc_con.SRC_VOCABULARY_ID ILIKE ‘%ATC%’
QUALIFY ROW_NUMBER_COUNTER_ATC = 1
)

SELECT
rx_superset.SRC_concept_name_RxNorm
, rx_superset.SRC_CONCEPT_ID_RXNORM
, rx_superset.SRC_CONCEPT_CLASS_ID_RxNorm
, rx_superset.SRC_CONCEPT_CODE
, rx_superset.SRC_VALID_START_DATE
, rx_superset.SRC_VALID_END_DATE
, rx_superset.SRC_STANDARD_CONCEPT
–Adding ATC codes from ATC_CODE
, ATC_CODE.SRC_ALL_ATCs
FROM
(
SELECT
rx_co.SRC_CONCEPT_ID AS SRC_CONCEPT_ID_RxNorm
, rx_co.SRC_CONCEPT_NAME AS SRC_CONCEPT_NAME_RxNorm
, rx_co.SRC_CONCEPT_CLASS_ID AS SRC_CONCEPT_CLASS_ID_RxNorm
, rx_co.SRC_VOCABULARY_ID
, rx_co.SRC_CONCEPT_CODE
, rx_co.SRC_VALID_START_DATE
, rx_co.SRC_VALID_END_DATE
, rx_co.SRC_STANDARD_CONCEPT
FROM
SPARKSOFT_DATA_SHARE.PUBLISHED_WEB.CONCEPT rx_co
WHERE
rx_co.SRC_VOCABULARY_ID = ‘RxNorm’
UNION
SELECT
rxe_co.SRC_CONCEPT_ID AS SRC_CONCEPT_ID_RxNorm
, rxe_co.SRC_CONCEPT_NAME AS SRC_CONCEPT_NAME_RxNorm
, rxe_co.SRC_CONCEPT_CLASS_ID AS SRC_CONCEPT_CLASS_ID_RxNorm
, rxe_co.SRC_VOCABULARY_ID
, rxe_co.SRC_CONCEPT_CODE
, rxe_co.SRC_VALID_START_DATE
, rxe_co.SRC_VALID_END_DATE
, rxe_co.SRC_STANDARD_CONCEPT
FROM
SPARKSOFT_DATA_SHARE.PUBLISHED_WEB.CONCEPT rxe_co
WHERE
rxe_co.SRC_VOCABULARY_ID = ‘RxNorm Extension’
)rx_superset
LEFT OUTER JOIN
ATC_CODE
ON rx_superset.SRC_CONCEPT_ID_RXNORM = ATC_CODE.SRC_MAPPED_CONCEPT_ID

#24

Your query references external data, so unfortunately I cannot check it.
If you want a hierarchy, you can use the existing hierarchy (and report back on your findings so that we can improve it :slight_smile: ).

You can chose the ATC level (1/2/3/4/5) that fits your purpose and the included RxNorms will depend on the level.

select *
from concept c
join concept_ancestor ca on ancestor_concept_id = c.concept_id
join concept c2 on descendant_concept_id = c2.concept_id
where c.vocabulary_id = ‘ATC’ and c2.vocabulary_id = ‘RxNorm’
;

#25

Could I request a clarification on what is meant by:

You can choose the ATC level that fits your purpose and the included RxNorms will depend on the level.

This is actually the very question I came to this thread to try and answer. Here’s the question: If I separately select:

*All RxNorm descendants of an ATC level 4 code from concept_ancestor (call that set A)
*All RxNorm descendants of the ATC level 3 code above that level 4 code (call that set B)
*All RxNorm descendants of the ATC level 2 code above that level 3 code (call that set C)

Can I expect that set C will contain the entirety of sets A and B within it? Said another way, I am trying to figure out if I start with a less-granular level of ATC, that I will still get all of the drugs that would have been descendants of the more granular ATCs below.

Thanks!

#26

Yes, you can expect that :slight_smile: (by the way, this is not some external knowledge, one can verify this by getting counts of descendants at different levels)

#27

It also works down to ATC level 5, btw. But if you try the same thing with the next level down (RxNorm Ingredient) it breaks down. One ingredient can be the child of several ATCs, but its children may not be also grandchildren of those ATC. It splits up again.

t