Standardizing procedures (call for non-US based collaborators)

Researchers
19

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;