Am I overlooking a relationship between RxNorm / NDC and category: “OTC” or “Prescription-required”?
It could be helpful for me to segregate meds using this criterion.
Thanks!
GP
I thought that I might have found it… Map from RxNorm to SPL, then look for concept class “OTC Drug” or “Prescription drug”.
But, Nope
create table clinical_drug as
select distinct
concept_name as RxNorm_name,
concept_ID as RxNorm_ID
from
vocab.concept
where
domain_id =‘Drug’ and
vocabulary_id=‘RxNorm’ and
concept_class_id=‘Clinical Drug’ and
invalid_reason is null and
standard_concept=‘S’
; N=34,858 Clinical drugs
create table spl_concepts as
select distinct
cd.*,
cr.concept_id_2 as SPL_ID
from
clinical_drug as cd
inner join
vocab.CONCEPT_relationship as cr
on
cd.RxNorm_id=cr.concept_id_1
where
cr.relationship_id=‘RxNorm - SPL’
;Maps to 210,066 SPL concepts
create table spl_class as
select distinct
spl.RxNorm_name,RxNorm_ID,
c2.concept_class_id as SPL_Class
from
spl_concepts as SPL
inner join
vocab.concept as c2
on SPL.SPL_ID=c2.concept_id
order by RxNorm_ID
; Which distills down to 11,377 unique SPL concept class records for those 34,858 clinical drugs
select count(*) as unique_RxNorm_IDs
from
(select unique RxNorm_ID
from spl_class)
; When I allow for there being multiple SPL Class records for some of the clinical drugs, I see that I can categorize no more than 10,008 of the 34,858 clinical drugs.
*** What am doing wrong? **
*** Am I encountering a limitation of the vocabularies as they are mapped here? **
*** Should I be looking to another resource for my purpose?**
