We discussed this in the vocab tutorial and the consensus was to use invalid_reason is null to get the correct concept. I have an unmapped concept example using this logic. It looks like LOINC code 13055-9 was updated to a new LOINC of 66483-9 in 2015. If I use the logic of “invalid_reason is null”, this ends up unmapped. The invalid_reason for 13055-9 is marked as ‘U’ and not null.
select * from omop_v5.concept src
left join omop_v5.concept_relationship cr on src.concept_id = cr.concept_id_1
and cr.relationship_id = 'Maps to'
and cr.invalid_reason is null
left join omop_v5.concept tar on cr.concept_id_2 = tar.concept_id
and tar.standard_concept = 'S'
and tar.invalid_reason is null
where src.concept_code = '13055-9'
and src.invalid_reason is null
;
Is this just a vocabulary error or a problem with how I am using the invalid_reason column?
Another example is NDC code 63323026920. This only links to a ‘D’ invalid_reason record in the concept table with a valid_end_date of 03-01-2015. My source record is from an encounter before that time.
There is a valid concept_relationship mapping to an valid rxnorm code from this ‘D’ record, but if I am using the “invalid_reason is null” logic, I will get an unmapped record.
Should the logic just look to see if the record was valid at the time of the encounter? This goes against the idea of “we found a better mapping”, but if codes are being deprecated after being used in the field and left unmapped it seems we are losing information.
How should we address getting data from providers who are using EMRs or systems that are using older datasets that are not updated?
BTW, the 9 digit NDC code 633230269 does have a valid mapping. The CR relationship was valid starting 01/29/2015.
NDC is a date centric vocabulary, so yes you need to look for the code valid at the time of dispensing. It is a known issue that the dates can be too tight, @Dymshyts and @Christian_Reich know about this issue and we chatted about it here.
Additionally, in our CDM_BUILDER if we don’t find an 11-digit NDC we then try for a 9-digit NDC. You should be doing that even aside of the date issue.
To your question above with the LOINC, I’m assuming that you are trying to get from LOINC to a standard terminology. This is how I would look for that mapping:
WITH CTE_VOCAB_MAP AS (
SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.concept_name AS SOURCE_CODE_DESCRIPTION, c.vocabulary_id AS SOURCE_VOCABULARY_ID,
c.domain_id AS SOURCE_DOMAIN_ID, c.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID,
c.VALID_START_DATE AS SOURCE_VALID_START_DATE, c.VALID_END_DATE AS SOURCE_VALID_END_DATE, c.INVALID_REASON AS SOURCE_INVALID_REASON,
c1.concept_id AS TARGET_CONCEPT_ID, c1.concept_name AS TARGET_CONCEPT_NAME, c1.VOCABULARY_ID AS TARGET_VOCABUALRY_ID, c1.domain_id AS TARGET_DOMAIN_ID, c1.concept_class_id AS TARGET_CONCEPT_CLASS_ID,
c1.INVALID_REASON AS TARGET_INVALID_REASON, c1.standard_concept AS TARGET_STANDARD_CONCEPT
FROM CONCEPT C
JOIN CONCEPT_RELATIONSHIP CR
ON C.CONCEPT_ID = CR.CONCEPT_ID_1
AND CR.invalid_reason IS NULL
AND cr.relationship_id = 'Maps To'
JOIN CONCEPT C1
ON CR.CONCEPT_ID_2 = C1.CONCEPT_ID
AND C1.INVALID_REASON IS NULL
UNION
SELECT source_code, SOURCE_CONCEPT_ID, SOURCE_CODE_DESCRIPTION, source_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID,
c1.VALID_START_DATE AS SOURCE_VALID_START_DATE, c1.VALID_END_DATE AS SOURCE_VALID_END_DATE,
stcm.INVALID_REASON AS SOURCE_INVALID_REASON,target_concept_id, c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, target_vocabulary_id, c2.domain_id AS TARGET_DOMAIN_ID, c2.concept_class_id AS TARGET_CONCEPT_CLASS_ID,
c2.INVALID_REASON AS TARGET_INVALID_REASON, c2.standard_concept AS TARGET_STANDARD_CONCEPT
FROM source_to_concept_map stcm
LEFT OUTER JOIN CONCEPT c1
ON c1.concept_id = stcm.source_concept_id
LEFT OUTER JOIN CONCEPT c2
ON c2.CONCEPT_ID = stcm.target_concept_id
WHERE stcm.INVALID_REASON IS NULL
)
SELECT *
FROM CTE_VOCAB_MAP
/*EXAMPLE FILTERS*/
WHERE SOURCE_VOCABULARY_ID IN ('LOINC')
AND TARGET_STANDARD_CONCEPT IS NOT NULL
AND SOURCE_CODE = '13055-9'
I will add the 9 digit NDC check to the code. And the date ranges.
For the LOINC question, your query works fine, but it does not check to see if the value of the source invalid_reason is null. The invalid_reason value for the code I was having problems with is ‘U’. I am fine ignoring the source invalid_reason value if that is the accepted practice, but the treatment of this column is one of the problems I am having understanding. During the vocab tutorial, Christian mentioned the ‘U’ value in passing, but it seemed the best practice was “invalid_reason is null” for all tables.
Checking the encounter_date instead of the invalid_reason would work in this instance, but that would not address the “better mapping” scenario.
My impression from the vocab tutorial was that you had to use the invalid_reason and start/end dates. When assigning the concept_ids to the source values (from the concept table), you need to make sure the date of the source value is within the start/end dates of the concept_id you are going to use. So in your first example, if your LOINC code 13055-9 is dated before 2015, then use the concept_id with the invalid_reason = U. If your LOINC code is dated after 2015, then concept_id = 0.
Now if you are looking for a mapping from (for example) ICD-9 to SNOMED and concept_relationship table has two mappings from the one ICD-9 code (source_concept_id) you are trying to add into the CDM by assigning a target_concept_id (SNOMED for example), use the current (dated at the time of the ETL) mapping record. That current mapping record should not have an invalid_reason populated.
I question that “you had to use the invalid_reason and start/end dates” to perform the mapping. See the thread Checking for INVALID_REASON for concepts found using ‘Maps To’ relationship which also talks about using the valid start and end dates. Here Christian Reich states
“NDC codes are reused so rarely, that we just switched off that feature. DRGs are the only ones where that works that way. So, all NDC codes are uniquely mapped, Which means you really need not check the dates.”
He says NDC in the context of the thread, but I put more emphasis on DRGs are the only codes where you need to check the date.
I think a generic set of step to map from source code to a standard concept is to:
Select from concept where concept_code = "source code"
If Not found or Invalid Reason = 'D' set standard_concept_id = 0, your done
If Invalid Reason is NULL goto step 3
Else Invalid Reason must be 'U' proceed to step 2
Get the replacement concept using the 'Concept replaced by' relationship
If nothing found, set Standard_concept_id = 0, tell Christian there is a vocabulary error
Using the concept id from Step 1 or the replace by concept id from Step 2 Get the standard concept by using the 'Maps to' relationship'
If concept_relationship invalid_reason is NOT NULL set standard_concept id = 0 you are done
