OHDSI Home | Forums | Wiki | Github

Where to find sample queries for drugs

Vocabulary Users
#1

Where might I find a collection of sample queries for navigating drugs?
I want to be able to identify products containing aspirin (that part was easy) and then segregating out drugs for which it is the sole active ingredient, with strength LE 325mg.(I plan to assume that drugs meeting that criteria are used for cardioprotective purposes.) Drug form will also be useful, as I doubt anyone uses meds with route other than PO for that purpose. I want to do something similar with NSAIDs.
Sample queries address these would be handy to learn from in constructing my own queries.

Hopefully I am posting this in the correct forum.

Thanks.
Gerry

#2

Never mind- I found http://vocabqueries.omop.org/system/app/pages/subPages?path=/drug-queries

#3

FYI - As a side project, my team is going to take what is on the site you mentioned above and rewrite them using OMOP CDM and vocabulary 5. We will be publishing them on the OHDSI wiki site instead of the omop.org site.

1 Like
#4

That’s terrific, @mvanzandt, it would be a very valuable resource for the
entire OHDSI community. Please let me know how I can help you move this
forward.

#5

@Patrick_Ryan, Anyway we can get the original document? We can copy and paste from the website, but would be much easier if we can have the original document to edit and then upload onto the wiki site. I’m kicking off the project tomorrow at a team meeting so will let you know how it goes.

1 Like
#6

@mvanzandt:

This is old material, so unlikely anybody has the complete set. But I found an old copy: Standardized Queries V4 0 OMOP_1.2.docx (416.3 KB). But it may deviate from what’s on the web.

#7

@mvanzandt Thank you! Adapting the queries that I found there is proving problematic. I suspect that I might be improperly selecting concept IDs from ATLAS or using wrong query.

In attempting to find NSAIDs I have been plugging various concepts into:
create table work.nsaid AS
SELECT
d.*
FROM refer.concept_ancestor as CA ,
refer.concept AS A,
Refer.concept AS D
WHERE CA.ancestor_concept_id = 4156857
AND CA.ancestor_concept_id = A.concept_id
AND CA.descendant_concept_id = D.concept_id
;

#8

According to ATLAS, 4333931 refers to the Pharmacologic Class (Domain=DRUG, VOCABULARY=NDFRT) “Nonsteroidal Anti-inflammatory Drug”. However, amongst other drugs, it finds Opium, vitamin B12, and quinine.

#9

I don’t have the original document, but @mkhayter and @DTorok may be able to share what they had originally done for OMOP here: http://vocabqueries.omop.org/.

#10

@pulver, try picking an ancestor concept from either ATC or ETC vocabularies…

#11

@Patrick_Ryan
Thank you

As I understand, the ETC vocabulary is only available to FDB customers and the.license is quite costly. I will try ATC.

Limiting to the ATC vocabulary, searching on “NON-STEROIDAL”, “NONSTEROIDAL”, “NON STEROIDAL” or “NSAID” is unsuccessful on ATLAS.

Is the work, by @mkhayter and @DTorok, to which you refer different from what I found at http://vocabqueries.omop.org/system/app/pages/subPages?path=/drug-queries ?

Best,
Gerry

#12

@pulver:

ATC doesn’t have NSAIDS. They have ATC2 21604253 “ANALGESICS” and below it ATC3 21604303 OTHER “ANALGESICS AND ANTIPYRETICS”. Other here stands for not opioids and not migraine medication.

#13

Thank you.
So, how would I uniquely identify NSAID?

I am looking through a medication reconciliation table and I want to find amongst the DRUG_EXPOSURE records NSAIDs and Aspirin (yes, I know that ASA is an NSAID).

Then I want to bifurcate the aspirin products between records that are likely being taken as CVD prophylaxis. (I figure that PO products, with strength of 325 mg or less, for which ASA is the sole active ingredient.)

#14

@Pulver Do you know the ingredients of all the NSAIDs you are looking for? If so, you can query the drug_exposure table looking for all the descendants of those ingredients. Done a similar project and that’s what we used to find all the drugs tied to NSAIDs.

#15

Here is a query that you can get to the descendants using the ingredient name based on the standard vocabulary.

select ca.descendant_concept_id from concept_ancestor ca
inner join concept c
on ca.ancestor_concept_id = c.concept_id
where lower(c.concept_name) in (‘diclofenac’, ‘meloxicam’,‘ibuprofen’, ‘oxaprozin’)
and c.standard_concept = ‘S’
and c.concept_class_id = ‘Ingredient’

This is the query from the OMOP tutorial that shows you how to query using the concept_ancestor table. This is only showing you how to query on one ingredient concept_ID. You can change it to an “IN” statement for multiple ingredient concept_ids.

The query is looking for people who took any drugs that has the ingredient of warfarin with a 6 month washout period before the first exposure to warfarin.

WITH CTE_DRUG_INDEX AS (
SELECT de.PERSON_ID, MIN(de.DRUG_EXPOSURE_START_DATE) AS INDEX_DATE
FROM DRUG_EXPOSURE de
WHERE de.DRUG_CONCEPT_ID IN (
SELECT DESCENDANT_CONCEPT_ID
FROM CONCEPT_ANCESTOR WHERE ANCESTOR_CONCEPT_ID = 1310149 /warfarin/
)
GROUP BY de.PERSON_ID
)
SELECT i.PERSON_ID, i.INDEX_DATE, op.OBSERVATION_PERIOD_START_DATE, op.OBSERVATION_PERIOD_END_DATE,
(i.INDEX_DATE-op.OBSERVATION_PERIOD_START_DATE) AS DAYS_BEFORE_INDEX
FROM CTE_DRUG_INDEX i
JOIN OBSERVATION_PERIOD op
ON op.PERSON_ID = i.PERSON_ID
AND i.INDEX_DATE BETWEEN op.OBSERVATION_PERIOD_START_DATE AND op.OBSERVATION_PERIOD_END_DATE
WHERE (i.INDEX_DATE-op.OBSERVATION_PERIOD_START_DATE) >= 180
ORDER BY i.PERSON_ID

#16

@mvanzandt Thank you, this gets me most of the way on the NSAIDs.
Are INactive ingredients included in ingredient lists? I am wondering whether if I exclude aspirin products for which there is a second ingredient, I will inadvertently lose those with enteric coating or buffers.

#17

http://vocabqueries.omop.org/ and
http://vocabqueries.omop.org/system/app/pages/subPages?path=/drug-queries

Look to be the same. I think it was just move from OMOP to IMEDS

#18

Thank you
-Gerry

t