Hi Vojta - reviving this thread because it might be helpful in a project I’m looking into. I’m trying to build a xwalk between US procedure CPT4s and ICD10PCS procedure codes. Based on this thread, I crosswalked CPT4 to the related SNOMED procedure concept and then mapped from the SNOMED concept to ICD10PCS.
Of course, since the SNOMED concepts (as mapped by concept_relationship) are much less granular than the CPT4 codes, I get multiple ICD10PCS codes per CPT4 code.
I’m wondering if you have built such a CPT4 to ICD10PCS xwalk or if you have access to code that achieves this.
For informational purposes, below is the SQL I used.
Any thoughts on this much appreciated.
thanks
Chris
with
CPT_lkp as
(
select
x.concept_id_1,
x.concept_id_2,
conc1.concept_code as concept_code1,
conc1.concept_name as concept_name1,
conc1.domain_id as domain_id1,
conc1.vocabulary_id as vocabulary_id1,
conc1.concept_class_id as concept_class_id1,
conc1.valid_end_DATE,
conc2.concept_code as concept_code2,
conc2.concept_name as concept_name2,
conc2.vocabulary_id as vocabulary_id2,
conc2.concept_class_id as concept_class_id2
from
`OMOP_vocabulary.concept_relationship` x
join `OMOP_vocabulary.concept` conc1 on x.concept_id_1 = conc1.concept_id
join `OMOP_vocabulary.concept` conc2 on x.concept_id_2 = conc2.concept_id
where
conc1.vocabulary_id = 'CPT4'
and conc2.vocabulary_id = 'SNOMED'
and conc2.concept_class_id = 'Procedure'
and x.relationship_id = 'Is a'
),
ICD_lkp as
(
select
x.concept_id_1,
x.concept_id_2,
conc1.concept_code as concept_code1,
conc1.concept_name as concept_name1,
conc1.domain_id as domain_id1,
conc1.vocabulary_id as vocabulary_id1,
conc1.concept_class_id as concept_class_id1,
conc2.concept_code as concept_code2,
conc2.concept_name as concept_name2,
conc2.vocabulary_id as vocabulary_id2,
conc2.concept_class_id as concept_class_id2
from
`OMOP_vocabulary.concept_relationship` x
join `OMOP_vocabulary.concept` conc1 on x.concept_id_1 = conc1.concept_id
join `OMOP_vocabulary.concept` conc2 on x.concept_id_2 = conc2.concept_id
where
conc1.vocabulary_id = 'ICD10PCS'
and conc2.vocabulary_id = 'SNOMED'
and conc2.concept_class_id = 'Procedure'
and x.relationship_id = 'Is a'
)
select
x.concept_code1 as cpt4_cd,
x.concept_name1 as cpt_desc,
x.vocabulary_id1 as code_type1,
x.valid_end_DATE,
x.concept_code2 as snomed_cd,
x.concept_name2 as snomed_desc,
y.concept_code1 as icd10pcs_cd,
y.concept_name1 as icd10pcs_desc,
y.vocabulary_id1 as code_type2
from
CPT_lkp x
join ICD_lkp y on x.concept_code2 = y.concept_code2
where
x.valid_end_DATE > '2020-01-01'
order by
1, 6;
