OHDSI Home | Forums | Wiki | Github

Flagging OTC products & What relationships are presently reliable?

Vocabulary Users
#1

Starting from a list of ingredients, I wanted to identify all of the products which contain them. I then wanted to add a variable for whether the product is OTC or Rx.

I believe that I am capturing the standard (RxNorm) concepts for the products. (Please correct me if I am wrong.)

create table ingredient_c as
select
c.concept_name,
c.concept_ID
from
ingredients as i left join
v5.concept as c
on
compress(upcase(i.ingredient))=compress(upcase(c.concept_name))
where
c.standard_concept = ‘S’ &
c.concept_class_ID = ‘Ingredient’ &
c.invalid_reason = ’ ’
order by ingredient;

create table drugs as
select distinct
a.concept_name as drug_name_RxNorm,
a.concept_ID as drug_ID_RxNorm,
b.concept_name as ingredient_name
from
ingredient_c as b ,
v5.concept as a ,
v5.concept_relationship as r
where
r.relationship_ID = ‘RxNorm has ing’ &
r.concept_ID_1 = a.concept_ID &
r.concept_ID_2 = b.concept_ID &
R.invalid_reason = ’ ’ &
A.invalid_reason = ’ ’
;

I found in the Concept_class table:
concept_class_id=‘OTC Drug’,concept_class_name=‘Human drug class Human OTC Drug’,concept_class_concept_id=45754858.
However, concept_class_id=‘OTC Drug’ is not in the Concept table.

A search of the Relationship table suggested 'Has legal category (SNOMED)’.

As my list is standardized on RxNorm, I tried to translate it from RxNorm to SNOMED.
CREATE TABLE SNOMED AS
select distinct
D.* ,
a.concept_name as drug_name_SNOMED ,
a.concept_ID as drug_ID_SNOMED
from
drugs as d ,
v5.concept as a ,
v5.concept_relationship as r
where
r.relationship_ID = ’ RxNorm - SNOMED eq’ &
r.concept_ID_1 = d.DRUG_ID_RxNorm &
r.concept_ID_2 = a.concept_ID &
r.invalid_reason = ’ ’ &
a.invalid_reason = ’ ’
;

Here I got stuck, as only 216 of 7593 rows were returned.

So I did a frequency count on Concept_Relationship for relationship_ID ’ RxNorm - SNOMED eq’ and found that, in total, there are only 18,849 rows with this relationship.

So two questions:
1. Am I approaching this in a reasonable way?
2. Is there a way for me to know what relationships can be depended on and which require further development before use?

Thanks,
Gerry

CONCEPT_SYNONYM has Invalid and nonstandard concepts present
#2

A for effort, @Pulver. :slight_smile: Couple points:

Can’t do that. Only works for Drug Forms and Drug Ingredients. To navigate from a drug product to Ingredient you need to use the CONCEPT_ANCESTOR table, since this is a multi-step relationship.

Don’t touch it. The only reliable place for OTC vs Prescription in the US is the concept_class_id in the Concepts of the “SPL” Classification vocabulary. What you need to do is to use the CONCEPT_ANCESTOR table again, and link each drug product to its SPL, and then hope that you get an unambiguous result.

#3

@Christian_Reich: Thank you. Today I shall explore the use of the concept_ancestor table. Should I expect to find that the 7593 rows that I found using the “RxNorm has ing” relationship are a subset of what I will get by using CONCEPT_ANCESTOR? I have not been familiar with SPL. I see that transitioning off of Medi-span will encourage my developing a good deal more understanding of drug related vocabularies. Always a good thing… I had become too reliant on Medi-span.

#4

@Christian_Reich

Using the ancestors, i get similar results (7508 products found). Perhaps you thought that I was seeking all of the branded variations?

I convert all drug_exposures to their generic representations using ‘maps to’, before using them, as I do not wish to differentiate between someone getting their 100 mg enteric coated drug X from Bayer or Pfizer. While working with Medi-span I translated NDC that was within EHRs to GPI and used GPI, at various classification levels, for all further work. My impression is that “Clinical Drug” concept is equivalent to 14 character GPI, in terms of specificity.

Am I misunderstanding something?

/* Find the standard concept_id for each of the ingredients of interest */
proc sql;
create table ingredient_c as
select
c.concept_name as ingredient_concept_name ,
c.concept_ID as ingredient_concept_ID
from
ingredients as i left join
v5.concept as c
on
compress(upcase(i.ingredient))=compress(upcase(c.concept_name))
where
c.standard_concept = ‘S’ &
c.concept_class_ID = ‘Ingredient’ &
c.invalid_reason = ’ ’
order by ingredient_concept_name;

create table test as
select
i.* ,
c.concept_id as drug_concept_ID ,
c.concept_name as drug_concept_name
from
ingredient_c as i join
v5.concept_ancestor as ca
on ca.ancestor_concept_id=i.ingredient_concept_id join
v5.concept as c
on c.concept_id=ca.descendant_concept_id
where
concept_class_ID = ‘Clinical Drug’
;

#5

@Pulver:

Not sure what you really are trying to achieve. Looks like you want to find all drug products containing an ingredient in a list you have, and then determine if they are OTC or prescription drugs. Correct?

If so, try this:

select distinct -- Because there are multiple trajectories from Ingredient to SPL
  spl.concept_class_id as spl_designation, 
  ing.concept_id as ing_id, ing.concept_name as ing_name
--  drug.concept_id as drug_id, drug.concept_name as drug_name -- Un-comment if you want to see the actual drug
from ingredient i -- Gerry's ingredients
join v5.concept ing on compress(upcase(i.ingredient))=compress(upcase(ing.concept_name)) -- Match ingredient concepts on name, which may not always work correct
  and ing.vocabulary_id='RxNorm' and ing.concept_class_id='Ingredient' -- Pick only RxNorm Ingredients
join v5.concept_ancestor a_down on a_down.ancestor_concept_id=ing.concept_id -- For looking down the hierarchy to the drug products
join v5.concept drug on drug.concept_id=a_down.descendant_concept_id -- Drug concepts
join v5.concept_ancestor a_up on a_up.descendant_concept_id=drug.concept_id -- For lookig up the hierarchy to the SPLs
join v5.concept spl on spl.concept_id=a_up.ancestor_concept_id and spl.vocabulary_id='SPL' -- SPL concepts
  and spl.concept_class_id in ('OTC Drug', 'Prescription Drug') -- kick out animal, device, all that junk

It will pick up the OMOP concept for your ingredients, then uses CONCEPT_ANCESTOR to find the drugs, then uses CONCEPT_ANCESTOR again to find the SPL for each drug.

1 Like
#6

@Christian_Reich

Thank you.

Ultimately, I am trying to create a variant of Drug_era for exposure to specific ingredients. I say variant because, for this project, which includes reconciliation as well as prescription records in Drug_Exposure, the logic for estimating the duration of exposure will be need to customized. The records that I obtain usually have no drug_exposure_end_date; quantity and days_supplied are even rarer.

My current thinking is that algorithm will consider such assumptions as that Amoxicillin is typically taken (I think) for five to fourteen days and an ACEi for cardiovascular disease is usually taken for life, unless the patient is switched to another ACEi or there is an explicit drug_exposure_end_date recorded. The maximum duration for which a prescription is valid, typically one year, but less for some DEA scheduled products also must be considered.

One factor that I briefly considered using was OTC vs. Rx; but I didn’t presently see utility in its inclusion. Nonetheless, as this is a characteristic that will be useful on other projects, I decided that I should learn how to differentiate between them.

#7

@Pulver:

This keeps coming up all the time. Folks say “The eras should have different parameters depending on the drug and the indication.” To which I usually respond “Tell me how to do it better, and we can implement.” Which usually ends the conversation right there. And the parameters are still no stockpiling and a standard persistence window of 30 days. You are the first one who actually is thinking of actually doing something about that. Keep it coming.

Couple ideas:

  • You may want to query the drug data, and infer patterns
  • You may want to use indication information and treatment guidelines
  • You can infer indication from the data as well

None of these are trivial. Good luck.

t