OHDSI Home | Forums | Wiki | Github

OMOP code crosswalks

vocabularies

(Adetomiwa O Oguntuga) #1

Hello,
I am very new to OMOP and i wanted to ask if anyone could point me in the direction of OMOP resources that map between vocabularies (for example NDC to RxNorm or ICD10 to SNOMED).

Thanks.


(Robert Miller) #2

You can download the vocabulary and mapping data using the ATHENA tool (link below).

How it works:
Data Model conventions
FAQ to this question (#11)

Relevant tables:
CONCEPT
CONCEPT_RELATIONSHIP
RELATIONSHIP

ATHENA (Download tool):
http://athena.ohdsi.org/


(Don Torok) #3

The basic query is to use the concept_relationship table for example to get
the RxNorm equivalent of a NDC code

SELECT ndc.concept_code, ndc.concept_id as source_concept_id,
ndc.concept_name, rxNorm.concept_id, rxNorm.concept_name
FROM concept ndc
JOIN concept_relationship r ON concept_id_1 = ndc.concept_id
JOIN concept rxnorm ON rxnorm.concept_id = concept_id_2
WHERE relationship_id = ‘Maps to’
and ndc.vocabulary_id = ‘NDC’ and ndc.source_code = ‘00719190213’


(Ajinkya Patale) #4

Is there a way we get the Crosswalk for
ICD9-ICD10-SNOMED
RxNrom-NDC
etc… etc
Basically I was looking for all the Patient table data to have a crosswalk… It would be great if we have it available via Athena…


(Don Torok) #5

This will give you crosswalk from ICD9/10 to SNOMED

CREATE OR REPLACE view source_to_concept_map_5
( source_vocabulary_id, source_concept_id, source_code, source_code_description, source_domain, source_standard_concept
, target_concept_id, target_vocabulary_id, target_concept_name, target_domain, target_standard_concept
)
AS
SELECT s.vocabulary_id AS source_vocabulary_id
, s.concept_id AS source_concept_id
, s.concept_code AS source_code
, s.concept_name AS source_code_description
, s.domain_id AS source_domain
, s.standard_concept AS source_standard_concept
, COALESCE(t.concept_id, 0 ) AS target_concept_id
, COALESCE(t.vocabulary_id, ‘None’ ) AS target_vocabulary_id
, COALESCE(t.concept_name, ‘No matching concept’ ) AS target_concept_name
, t.domain_id AS target_domain
, t.standard_concept AS target_standard_concept
FROM concept s
LEFT OUTER JOIN concept_relationship rel ON concept_id_1 = s.concept_id
AND relationship_id = ‘Maps to’
AND rel.invalid_reason IS NULL
LEFT OUTER JOIN concept t ON t.standard_concept = ‘S’ AND t.concept_id = concept_id_2
WHERE s.vocabulary_id IN( ‘ICD9CM’, ‘ICD10CM’ ) ;


t