OHDSI Home | Forums | Wiki | Github

Replacing GPI


(Gerald Pulver) #1

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?


(Anna Ostropolets) #2

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:
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.

(Hamed Abedtash) #3

Hi @Pulver

The mapping is still in progress, but I can share a “preview” of ATC mappings for HMG CoA reductase inhibitors. Is this the only class you need now?

(Gerald Pulver) #4

Thank you!
To be sure that I am understanding correctly -

  1. Are the mappings between NDC & RxNorm and RxNorm & ATC comprehensive, or nearly so?

  2. When you speak of “refactoring”, do you mean more fully populating relationships and/or adding new relationships? Or perhaps something else?

  3. What additional functionality is being introduced this summer?

  4. What lag time should be expected between the creation of a new NDC and its mapping t inclusion in the vocabularies?

Hopefully my weaning from Medispan won’t be too painful. :slight_smile:


(Gerald Pulver) #5

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.

(Anna Ostropolets) #6

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 :slight_smile:

(Gerald Pulver) #7

Thank you!

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?


  1. Antigonadotropic agents
  2. Androgens
  3. Anabolic steroids
  4. AntiThyroid agents
  5. Antidepressants
  6. Anticonvulsants
  7. Antipsychotics
  8. Digestive Enzymes
  9. Gallstone solubilizing agents
  10. GI Stimulants
  11. GI Chloride channel activators
  12. 5 amino salicylates
  13. Glucocorticoids
  14. Hypnotics
  15. Hypnotics excluding anti-histamine products
  16. Inhaled bronchodilators, excluding steroids
  17. Mast cell stabilizers
  18. Steroid inhalants
  19. Vaccines
  20. Toxoids
  21. Passive immunizing agents
  22. Anticholinergic bronchodilators
  23. Bisphophonates
  24. AntiRetrovirals
  25. Antimicrobials (systemic)

(Hamed Abedtash) #8

Hi again,

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.

(Gerald Pulver) #9

Thank you!

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?

(Christian Reich) #10

Called a concept set in ATLAS: http://www.ohdsi.org/web/atlas/#/conceptsets. I’d define the RxNorm or RxNorm Extension ingredients and all descendants.

(Gerald Pulver) #11

Thank you. Perfect. When I start having stuff to offer, I will get in touch with you to ask about the mechanics of contributing.