OHDSI Home | Forums | Wiki | Github

How to extract CPT-4 to SNOMED category/equivalent (OMOP)

Implementers
#1

Hi, I’m new to OHDSI, so maybe this is a basic question. I am attempting to automate the CPT-4 to SNOMED category/equivalent (OMOP) relationship using the downloaded vocabularies, but cannot see how to recreate.

For an example, I can use the ATHENA browser to search CPT4 code 43771 which results in conept code 2108987. In the Term connections I see CPT-4 to SNOMED category (OMOP) Laparoscopic adjustable gastric banding 4190525 SNOMED.

I have downloaded (and applied cpt4.jar) CPT vocaulary and the SNOMED vocabs. I can find 2108987 in CPT4 and 4190525 in SNOMED but I cannot find any links between. What am I missing?

Thanks for your help!
Gene

#2

Link from the source code to the OMOP standard concept using the concept relationship table, where the relationship id = ‘Maps to’

e.g.
SELECT COALESCE( c2.concept_id, 0 ) as target_concept, c2.domain_id AS target_domain
FROM concept c1
LEFT OUTER JOIN concept_relationship ON relationship_id = ‘Maps to’ AND concept_id_1 = c1.concept_id
LEFT OUTER JOIN concept c2 on c2.concept_id = concept_id_2
WHERE c1.vocabulary_id = ‘CPT4’ AND c1.concept_code = ‘43771’;

target_concept target_domain
2108987 Procedure
#3

Thank you Don, but the results I am looking for are:

select
<some code>
WHERE c1.vocabulary_id = ‘CPT4’ AND c1.concept_code = ‘43771’;

CPT-4 Concept code | Relationship | Relates to | Concept id | Vocabulary
43771 | CPT-4 to SNOMED category (OMOP) | Laparoscopic adjustable gastric banding | 4190525 | SNOMED

Thanks!

#4

I am pretty sure that

FROM concept c1
LEFT OUTER JOIN concept_relationship ON relationship_id = ‘Maps to’ AND concept_id_1 = c1.concept_id
LEFT OUTER JOIN concept c2 on c2.concept_id = concept_id_2

is your :confused:

#5

The only lines from concept_relationship where concept_id_1 = 2108987 are:

concept_id_1 concept_id_2 relationship_id valid_start_date valid_end_date invalid_reason
105530 2108987 45887523 Is a 19700101 20991231 NaN
655140 2108987 2108987 Maps to 20060213 20991231 NaN
655806 2108987 2108987 Mapped from 20060213 20991231 NaN
#6

Just to make sure. Did you select both CPT4 and SNOMED and download vocabularies together, or downloaded separately? If you don’t see the relation to SNOMED in concept_relationship table from CPT4, it seems like you have downloaded vocabularies separately. Downloading the vocabulary together should fix the issue.

#7

Hello @Gene_Urrutia,

I’ve just checked Concept Relationship using

SELECT * FROM concept_relationship WHERE concept_id_1 = 2108987

and there’re extra entries for CPT4 - SNOMED mapping, with relationship_id = ‘CPT4 - SNOMED cat’ and ‘CPT4 - SNOMED eq’:

concept_id_1 concept_id_2 relationship_id valid_start_date valid_end_date invalid_reason
2108987 4041249 CPT4 - SNOMED cat 1980-01-01 2099-12-31
2108987 4041249 CPT4 - SNOMED eq 2014-04-01 2099-12-31
2108987 4190525 CPT4 - SNOMED cat 1980-01-01 2099-12-31
2108987 4190525 CPT4 - SNOMED eq 2014-04-01 2099-12-31

You’ve mentioned that you used cpt4.jar – I believe these extra rows should’ve been added by it. Strange that you can’t find them.

#8

@ shinjinaka

Yes, I downloaded separately. I will download together, and that should fix it. Thanks all!

Update:
Working as expected now. Thanks!

t