Can somebody provide code for mapping an RxNorm ‘Clinical Drug’ to a standard_concept in the Route domain?
Thank you for your question @cmeister . Most dosage forms can be clearly associated with a specific route of administration, though there are some exceptions. Even though a Clinical Drug has a form, the route does not always correspond to that, thus we do not have such relationship between Clinical Drug and Route in vocabularies. Kindly refer to this OHDSI poster on the new route of administration hierarchy that may be of more help with this topic.
@cmeister you can try to use this code for mapping an RxNorm ‘Clinical Drug’ to a standard_concept in the Route domain:
Summary
with tab as (
SELECT concept_id,
concept_name,
regexp_replace(concept_name, '.*(Tablet|Capsule|Injection|Solution|Suspension|Cream|Ointment|Patch|Spray|Inhaler|Suppository|Drops|Shampoo|Lotion|Gel|Powder|Foam|Emulsion|Film-coated tablet|Chewable tablet|Effervescent tablet|Extended-release tablet|Delayed-release tablet|Sublingual tablet|Buccal tablet|Lozenge|Troche|Oral solution|Oral suspension|Oral powder|Oral spray|Oral film).*', '\1') AS Dosage_Form,
CASE
WHEN concept_name ~* 'Tablet|Capsule|Chewable tablet|Effervescent tablet|Extended-release tablet|Delayed-release tablet|Film-coated tablet|Oral solution|Oral suspension|Oral powder|Oral spray|Oral film' THEN 'Oral'
WHEN concept_name ~* 'Injection|Prefilled syringe' THEN 'Intramuscula'
WHEN concept_name ~* 'Injectable Solution' THEN 'Intravenous'
WHEN concept_name ~* 'Intravenous Solution' THEN 'Intravenous'
WHEN concept_name ~* 'Topical Solution' THEN 'Topical'
WHEN concept_name ~* 'Ophthalmic Solution' THEN 'Ophthalmic'
WHEN concept_name ~* 'Otic Solution' THEN 'Otic'
WHEN concept_name ~* 'Nasal Solution' THEN 'Nasal'
WHEN concept_name ~* 'Oral Suspension' THEN 'Oral'
WHEN concept_name ~* 'Topical Suspension' THEN 'Topical'
WHEN concept_name ~* 'Ophthalmic Suspension' THEN 'Ophthalmic'
WHEN concept_name ~* 'Otic Suspension' THEN 'Otic'
WHEN concept_name ~* 'Cream|Ointment|Lotion|Gel|Foam|Shampoo|Emulsion' THEN 'Topical'
WHEN concept_name ~* 'Patch' THEN 'Transdermal'
WHEN concept_name ~* 'Nasal Spray' THEN 'Nasal'
WHEN concept_name ~* 'Oral Spray' THEN 'Oral'
WHEN concept_name ~* 'Topical Spray' THEN 'Topical'
WHEN concept_name ~* 'Inhaler' THEN 'Inhalation'
WHEN concept_name ~* 'Rectal Suppository' THEN 'Rectal'
WHEN concept_name ~* 'Vaginal Suppository' THEN 'Vaginal'
WHEN concept_name ~* 'Ophthalmic Drops' THEN 'Ophthalmic'
WHEN concept_name ~* 'Otic Drops' THEN 'Otic'
WHEN concept_name ~* 'Nasal Drops' THEN 'Nasal'
WHEN concept_name ~* 'Sublingual tablet' THEN 'Sublingual'
WHEN concept_name ~* 'Buccal tablet' THEN 'Buccal'
WHEN concept_name ~* 'Lozenge|Troche' THEN 'Oral'
ELSE 'Unknown'
END AS Route
FROM
concept
where vocabulary_id = 'RxNorm'
and concept_class_id='Clinical Drug')
select tab.concept_id, tab.concept_name, tab.route, c.concept_id, c.concept_code, c.concept_name,c.concept_class_id, c.standard_concept, c.invalid_reason, c.domain_id, c.vocabulary_id
from tab
join concept c
on tab.route=c.concept_name
and c.domain_id='Route'
and c.standard_concept='S';
This is just a code example and not a systematic approach. A more accurate approach in this case would be mapping from ‘Clinical drug form’ instead of ‘Clinical Drug’.
You can also find more detailed information about route hierarchy through linkage to dose form here.
Perhaps we can provide solution (/try to solve it) for the 99.9% of drugs and mark the exceptions somehow.
@cmeister Do you need it solved perfectly or 99.9% is good enough. We can structure the solution by ingredient. We can identify a list of exceptional ingredient where this is hard and for the easy ones the problem would be solved 100%.