© 2012-2014 Reagan-Udall Foundation for FDA (RUF)
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://imeds.reaganudall.org/publiclicense.
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Any redistribution of this work or any derivative work or modification based on this work should be accompanied by the following source attribution: "This work is based on work by the Innovation in Medical Evidence Development and Surveillance (IMEDS) and used under license at http://imeds.reaganudall.org/publiclicense.
Any scientific publication that is based on this work should include a reference to http://imeds.reaganudall.org.
This document reflects the implementation
for the Standard Vocabulary which is part of the Observational Medical Outcomes
Partnership (OMOP) Common Data Model, version 4.0 and covers the following
vocabulary domains:
Each section defines the loading and transformation of a vocabulary. The transformation turns the orginal format of a vocabulary as published by the source into the format of the OMOP CDM. This could be either the source_to_concept_map (non-standard vocabulary mapping table to standard vocabulary), or the concept, concept_relationship and concept_ancestor tables (standard vocabularies). The audiences for this document are developers who want to be able to repeat the vocabulary load process and extend vocabulary data to include information that have not been included by OMOP.
Note: Make sure you perform updates in priority order.
|
VOCABULARY_ID
|
VOCABULARY_NAME
|
URL
|
PRIORITY
|
|
0
|
IMEDS Vocabulary v4.4 2014-04-11
|
https://sites.google.com/site/omopvocabularyload/home
|
0
|
|
1
|
SNOMED-CT
|
https://sites.google.com/site/omopvocabularyload/condition-domain/snomed
|
1
|
|
2
|
ICD-9-CM
|
https://sites.google.com/site/omopvocabularyload/condition-domain/icd-9-cm
|
5
|
|
3
|
ICD-9-Procedure
|
https://sites.google.com/site/omopvocabularyload/procedure-domain/icd-9-procedure
|
6
|
|
4
|
CPT-4
|
https://sites.google.com/site/omopvocabularyload/procedure-domain/cpt-4
|
7
|
|
5
|
HCPCS
|
https://sites.google.com/site/omopvocabularyload/procedure-domain/hcpcs
|
8
|
|
6
|
LOINC
|
https://sites.google.com/site/omopvocabularyload/observation-domain/loinc
|
9
|
|
7
|
NDF-RT
|
https://sites.google.com/site/omopvocabularyload/drug-domain/ndf-rt
|
3
|
|
8
|
RxNorm
|
https://sites.google.com/site/omopvocabularyload/drug-domain/rxnorm-08-loading
|
2
|
|
9
|
NDC
|
https://sites.google.com/site/omopvocabularyload/drug-domain/ndc
|
4
|
|
10
|
GPI
|
|
10
|
|
11
|
UCUM
|
https://sites.google.com/site/omopvocabularyload/observation-domain/ucum
|
11
|
|
12
|
HL7 Administrative Sex
|
|
11
|
|
13
|
CDC Race
|
https://sites.google.com/site/omopvocabularyload/demographic-domain/race
|
11
|
|
14
|
CMS Place of Service
|
https://sites.google.com/site/omopvocabularyload/visit-domain/cms-place-of-service
|
11
|
|
15
|
MedDRA
|
https://sites.google.com/site/omopvocabularyload/condition-domain/meddra
|
10
|
|
16
|
Multum
|
https://sites.google.com/site/omopvocabularyload/drug-domain/multum
|
11
|
|
17
|
Read
|
|
10
|
|
18
|
OXMIS
|
|
11
|
|
19
|
FDB Indication/Contraindication
|
https://sites.google.com/site/omopvocabularyload/drug-domain/fdb-indication
|
11
|
|
20
|
FDB ETC
|
https://sites.google.com/site/omopvocabularyload/drug-domain/fdb-etc
|
10
|
|
21
|
WHO ATC
|
https://sites.google.com/site/omopvocabularyload/drug-domain/who-atc
|
10
|
|
22
|
Multilex
|
https://sites.google.com/site/omopvocabularyload/drug-domain/multilex
|
11
|
|
24
|
OMOP Visit
|
|
11
|
|
28
|
VA Product
|
https://sites.google.com/site/omopvocabularyload/drug-domain/va-product
|
11
|
|
31
|
SMQ
|
|
11
|
|
32
|
VA Class
|
https://sites.google.com/site/omopvocabularyload/drug-domain/va-class
|
10
|
|
33
|
Cohort
|
|
99
|
|
34
|
ICD-10-CM
|
|
5
|
|
35
|
ICD-10-PCS
|
|
6
|
|
36
|
IMEDS Drug Exposure Type
|
|
99
|
|
37
|
IMEDS Condition Occurrence Type
|
|
99
|
|
38
|
IMEDS Procedure Occurrence Type
|
|
99
|
|
39
|
IMEDS Observation Type
|
|
99
|
|
40
|
DRG
|
|
11
|
|
41
|
MDC
|
|
11
|
|
42
|
APC
|
|
11
|
|
43
|
Revenue Code
|
|
11
|
|
44
|
Ethnicity
|
|
11
|
|
45
|
OMOP Death Type
|
|
99
|
|
46
|
NLM Mesh
|
https://sites.google.com/site/omopvocabularyload/drug-domain/nlm-mesh
|
11
|
|
47
|
NUCC
|
|
11
|
|
48
|
CMS Specialty
|
|
11
|
|
49
|
LOINC Multidimensional Classification
|
|
11
|
|
50
|
FDA SPL
|
https://sites.google.com/site/omopvocabularyload/drug-domain/fda-spl
|
11
|
|
53
|
FDB Genseqno
|
|
99
|
|
54
|
CCS
|
|
99
|
Updating CONCEPT_ANCESTOR.
Creates the ancestry data between concepts based on their relationships.
1. Generate unique ancestor-descendant concept pairs from the staged data
$ sqlplus RXNORM_20120702/myPass@DEV_VOCAB @xx_build_stage_ancestry.sql
2. Verify effect of data load on DEV.CONCEPT_ANCESTOR table will have
In this step we will verify that number of active records in staging tables is valid prior to transfer data to DEV schema.
a. Number of Records in stage table
b. Number of records in DEV schema (prior load production)
In most cases number of records in stage (a) should be greater than number of active records in production (b)
In rare occasion that might not be the case. It is responsibility of the developer running the scripts to verify that that is the the valid.
Execute the following SQL commands:
$ sqlplus RXNORM_20120702/myPass@DEV_VOCAB
Execute the following SQL commands:
SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt
FROM CONCEPT_ANCESTOR_STAGE c
UNION ALL
SELECT '- Num Rec in DEV' AS scr, COUNT (8) cnt
FROM DEV.CONCEPT_ANCESTOR da ; |
Current Result: - Num Rec in stage | 8,510,646 |
- Num Rec in DEV | 6,051,471 |
3. Insert unique ancestor-descendant concept pairs from the staged data CONCEPT_ANCESTOR_STAGE into DEV.CONCEPT_ANCESTOR
$ exit | sqlplus RXNORM_20120702/myPass@DEV_VOCAB @xx_load_ancestry.sql
4. Generate unique onsie concept pairs from the staged data
NDF-RT and NDF class designations are defined for drug product (clinical and branded drug) and ingredient based concepts. If a certain class happens to be defined for products only, and not for the ingredients that are part of the product, OMOP adds an additional relationship inferring this lacking relationship.
Since drug products can contain more than one ingredient, and a given class designation could be assigned for either ingredient or the comibination thereof, the OMOP inferred relationships will only be based on single-ingredient drugs, where this can be done unambiguously.
$ exit | sqlplus RXNORM_20120702/myPass@DEV_VOCAB @xx_build_stage_onsie.sql
5. Repeat ## 1, 3 for rebuilding ancestor-descendant concept pairs after "onsie"-generation.
