To select drugs by category I have typically been using GPI to find qualifying NDC on Medi-Span. (Ingredient records are also useful.) I subsequently map to the corresponding OMOP concepts using CONCEPT and CONCEPT_Relationship tables. Soon, our Medi-Span license will be expiring; thus I need to adapt my processes accordingly.
From what I understand, so far, it appears that the ATC vocabulary will be my best bet for such selections as STATIN drugs. More complex queries would be required for example to find “orally administered medicines containing 325 mg or less of aspirin with no active ingredients other than buffers”.
Where will I find examples of queries to use as models in creating my own?
ATC fits the best indeed; its refactoring is currently in progress, so no decent documentation right now. But the cookbook with the SQL queries will be ready later this spring/summer.
By that time ATC coverage will be broader and more precise, but even now you can look for your drugs of interest.
For instance,
you first find the broad group of ATC codes for statins: https://www.whocc.no/atc_ddd_index/?code=C10AA
then you query the database to look for all children of this broad group:
select c2.* from concept c
join concept_ancestor ca on ca.ancestor_concept_id = c.concept_id
join concept c2 on c2.concept_id = ca.descendant_concept_id
where c.concept_code like ‘C10AA%’;
For aspirin:
you have multiple aspirins with different indications, so you need to browse WHO ATC and figure out the class you want. Say, you want this one: https://www.whocc.no/atc_ddd_index/?code=B01AC06 (the daily dosage of others is higher than 325 mg).
The same thing:
select c2.* from concept c
join concept_ancestor ca on ca.ancestor_concept_id = c.concept_id
join concept c2 on c2.concept_id = ca.descendant_concept_id
where c.concept_code like ‘B01AC06’;
You don’t really know the indication, you can use drug_strength to limit the results by the dose:
select c2.* from concept c
join concept_ancestor ca on ca.ancestor_concept_id = c.concept_id
join concept c2 on c2.concept_id = ca.descendant_concept_id
join drug_strength ds on ds.drug_concept_id = c2.concept_id
where lower(c.concept_name) = ‘aspirin’
and c.standard_concept = ‘S’
and c.concept_class_id = ‘Ingredient’
and ds.amount_value<325
and not exists
(select 1 from drug_strength ds2
where ds2.drug_concept_id = ds.drug_concept_id
group by ds2.drug_concept_id having count(1)>1);
Here, I’m using only amount_value as I’m assuming that you need only solid forms. Otherwise, add numerator_value/denominator_value for concentration.
I’m also adding a group by condition to get only single-ingredient drugs. For buffers, find their concept_ids and put in the query as well.
Hi!
Actually, those were just examples of queries that I might do. I have many queries that I use for creating specialized value sets of drugs which might be used primarily in monitoring for safety and quality of care.
Thanks,
Gerry
NDC-RxNorm covers most of the important drugs, but not all.
ATC-RxNorm is pretty bad, so it will be changed significantly, both the coverage and the existing relationships.
If you submit your issue to the Vocabulary v5.0 GitHub page, you can specify there how urgent it is
It’s a long separate topic. Let it happen first and you’ll get a full description
Here’s a more extensive sample of drug categories for which I’ve needed to identify exposures recently. Doing so through Medispan is relatively straightforward. Will it be so using ATC as well? Or would I need to manually search for ingredients or other characteristics?
Some of them form the list follow ATC hierarchy (for example, vaccines, antipsychotics) but some other not (for example, mast cell stabilizers). Take a look at ATC structure on WHO website to see how they match: https://www.whocc.no/atc_ddd_index/
You may need to regroup some 5th level ATC classes to your specified grouping if the exact class does not exist in ATC.
We will provide full mapping from NDCs to RxNorm to ATC, so you will be able to identify corresponding drug products to ATC classes whether they are coded in NDC or RxNorm.
This sounds good. When I create a custom grouping that does not directly line up with the ATC structure, is there a mechanism where I could offer it to the community, so that others won’t need to re-invent the wheel?
