Recovering drug information from the OMOP tables

Implementers
1

Hi everyone,
I am trying to understand how to get the information about drugs from the drug_exposure table. Almost all the fields appear as NAs to me.
Moreover, I need to recover the dm+d and BNF codes for the drugs. I have drawn this SQL query which is not giving me back good results:
SELECT
de.person_id AS eid,
de.drug_exposure_id,
de.drug_concept_id,
de.provider_id AS data_provider,
c.concept_code AS drug_code,
c.vocabulary_id AS drug_vocabulary,
c.concept_name AS drug_name,
de.quantity
FROM
drug_exposure de
LEFT JOIN
concept_relationship cr ON de.drug_concept_id = cr.concept_id_1 AND cr.relationship_id = ‘Mapped from’
LEFT JOIN
concept c ON cr.concept_id_2 = c.concept_id AND c.vocabulary_id IN (‘DM+D’,‘BNF’)
ORDER BY
de.person_id, de.drug_exposure_start_date

Maybe I am missing some connections through the tables?
If someone can help, it would be appreciated.

Best,
Leonardo

2

Hi @LeoVincenzi:

Welcome to the family.

Looks good to me, except the vocabulary_id is “dm+d” (lower case, ask the NHS Technology Reference Update Distribution why), and “BNF” we don’t have in the public domain OHDSI Standardized Vocabularies (but you may have that locally). What data do you have, actually?

3

Hi @Christian_Reich,
So, I am working on the AllOfUs database.
I went a bit forward in the query development, and I reached that point:
SELECT
de.person_id AS eid,
de.drug_exposure_start_date AS issue_date,
cp.concept_name AS data_provider,
c.concept_code AS drug_code,
c.concept_name AS drug_name,
de.quantity
FROM
drug_exposure de
LEFT JOIN
concept c ON de.drug_concept_id = c.concept_id AND c.vocabulary_id IN (‘RxNorm’,‘RxNorm Extension’) AND c.domain_id IN (‘Drug’)
LEFT JOIN
concept_relationship cr ON de.provider_id = cr.concept_id_1 AND cr.relationship_id=‘Mapped from’
LEFT JOIN
concept cp ON cr.concept_id_2 = cp.concept_id AND cp.domain_id IN (‘Provider’)
ORDER BY
eid, issue_date

where I have for each person the account of the drug code and its name on each row. As you mentioned, BNF was not present, but I was unable to get some codes related to ‘dm+d’: it seems no entries in the concept table have a vocabulary_id corresponding to this string. According to Athena, there should be, but tests tell me something different.

Data providers still have the problems of NAs.

4

That is easy enough to check:

SELECT COUNT(concept_id)
FROM concept
WHERE vocabulary_id ILIKE ‘%dm+d%’;

For me that returns 405,431, same as Athena.

5

Hello @LeoVincenzi and welcome to OHDSI!

Please tell us more about why you need dm+d and BNF codes. Unless your source data have these drug vocabularies, you won’t get this information from the Drug Exposure table. RxNorm and RxNorm Extension are the two primary, standard vocabularies in the Drug Exposure table.

Also, AllOfUs is a US database and dm+d vocabulary is from the UK. So, if your source is only AllOfUs, then you won’t find dm+d in there.

1 Like