We are trying to utilize the concept table and build an interface where we want to show the cross walk as well. Is there a way we can use the codes in Concept table and create crosswalk for them and store as a separate table in our metadata… It helps user to view the relevant code in different vocab. For example if I am looking at 250.0 in ICD 9 , We wanted the ability to also look at the relevant ICD10/SNOMED code for 250.0 in the interface. It will be great if we can get a downloadable file from Athena or any logic which we can use to create these crosswalk for all the codes in Athena.
You can see the source code for the concept_id in ATLAS.
For the mapping table between ICD10 and SNOMED, the code below might be helpful.
SELECT source_code,target_concept_id, domain_id, X.vocabulary_id
INTO #ICD_source_to_concept
FROM @vocabulary_database_schema.CONCEPT
JOIN (SELECT concept_code as source_code, concept_id_2 as target_concept_id, vocabulary_id
FROM @vocabulary_database_schema.CONCEPT_RELATIONSHIP
JOIN @vocabulary_database_schema.CONCEPT
ON CONCEPT_RELATIONSHIP.concept_id_1 = CONCEPT_ID
WHERE CONCEPT.vocabulary_id = ‘ICD10’
AND CONCEPT_RELATIONSHIP.relationship_id = ‘maps to’
AND CONCEPT.invalid_reason IS NULL) X
ON concept.concept_id = x.target_concept_id
WHERE concept.invalid_reason is null
AND CONCEPT.standard_concept IS NOT NULL