SOURCE_TO_CONCEPT_MAPs Missing in

[ericaVoss]

This is for v5.0 17-JUL-15.

I check for certain maps to exist within the VOCABULARY. I'm finding that some do not exist anymore. The first I would expect, but not sure how important the second two are.

ICD9CM <> MedDRA
ICD9Proc <> RxNorm
ICD10PCS <> SNOMED

I used a query like this to find if the maps were there or not.

WITH CTE_VOCAB_MAP AS (
       SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.vocabulary_id AS SOURCE_VOCABULARY_ID, 
                           c.domain_id AS SOURCE_DOMAIN_ID, c.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, c.INVALID_REASON AS SOURCE_INVALID_REASON,
                           c1.concept_id AS TARGET_CONCEPT_ID, 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_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, 
                           stcm.INVALID_REASON AS SOURCE_INVALID_REASON,
        target_concept_id, 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 ('ICD9CM')
AND TARGET_VOCABUALRY_ID IN ('MedDRA') 

[cgreich]

@ericaVoss:

Not quite sure what's going on here:

1. ICD-9-CM to MedDRA is alive and kicking (even though we don't particularly like it):

select c1.concept_id as c1_id, c1.concept_name as c1_name, r.relationship_id as rel, r.invalid_reason as r_ir, c2.concept_id as c2_id, c2.concept_name as c2_name, c2.vocabulary_id as c2_vocab from concept c1 join concept_relationship r on r.concept_id_1=c1.concept_id and r.invalid_reason is null join concept c2 on c2.concept_id=r.concept_id_2 where c1.vocabulary_id='ICD9CM' and c2.vocabulary_id='MedDRA'

1. Did we ever have them? There aren't any concrete drugs mentioned in ICD-9-Proc, are there?
2. ICD-10-PCS is not released yet. Whatever was there before was junk from the infamous March 2014 release.

[cgreich]

Stupid auto-numbering gives me twice number one. Assume the second one is 2, the 2 after that is 3.

[ericaVoss]

@cgreich

1) ICD9CM <> MedDRA I see why, I said to only use REALTIONSHIP_ID of 'Maps To' but this uses 'ICD9CM-MedDRA'. Why does it fall under this certain type of RELATIONSHIP_ID?

2) ICD9Proc <> RxNorm I thought we did, but I'm not worried about this one.

3) ICD10PCS <> SNOMED Okay.

[cgreich]

1) Because "Maps to" maps from a Source to a Standard Concept. MedDRA are all Classification Concepts. That's why I don't want them there at all. 2) I think there are no procedure drugs in there. 3) Working on it right now. Got another input from the NLM on Tuesday.

[ericaVoss]

1) Got it! I was using our SOURCE_CODE --> STANDARD CONCEPT query to test these maps. That was my fault.

2) -

3) Thank you!

I'm going to close this, thank you!