Is there a way to have Usagi match source code string to concept code string? Instead of semantically similar terms? My procedure source terms are producing high match scores to similar procedures, but not the exact CPT source procedure. Going line by line to pick out the exact match would take forever.
You shouldn’t need to use Usagi at all if you know your source codes are in CPT, Can you use the Vocabulary ‘maps to’ concept relationships to find the appropriate standard concept? You’d use a exact match on the CPT code from your source with the source_code column in vocabulary.
Agree with @Chris_Knoll here. Here is an example query on how to do that.
WITH CTE_VOCAB_MAP AS (
SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.concept_name AS SOURCE_CODE_DESCRIPTION, c.vocabulary_id AS SOURCE_VOCABULARY_ID,
c.domain_id AS SOURCE_DOMAIN_ID, c.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID,
c.VALID_START_DATE AS SOURCE_VALID_START_DATE, c.VALID_END_DATE AS SOURCE_VALID_END_DATE, c.INVALID_REASON AS SOURCE_INVALID_REASON,
c1.concept_id AS TARGET_CONCEPT_ID, c1.concept_name AS TARGET_CONCEPT_NAME, 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
/*We use the SOURCE_TO_CONCEPT_MAP table to store site specific terminologies that we have mapped ourselves*/
SELECT source_code, SOURCE_CONCEPT_ID, SOURCE_CODE_DESCRIPTION, source_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID,
c1.VALID_START_DATE AS SOURCE_VALID_START_DATE, c1.VALID_END_DATE AS SOURCE_VALID_END_DATE,
stcm.INVALID_REASON AS SOURCE_INVALID_REASON,target_concept_id, c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, 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 = 'CPT'
AND SOURCE_CODE = '36555' /*CPT*/
Let us know if we are still missing your question!
1 Like
I like that query @ericaVoss. Did you think about putting it into a ‘gist’? It’s a sorta github storage for code fragments that you can revise, people can fork, etc. THink of it as a git repository that people can fork individual things. Here’s the gist’s i’ve made:
If you just go to https://gist.github.com/ and then click ‘new gist’ at the top right, you should be able to drop this in a new file, give it a name, and then people will be able to find it via searching. (They can find that here too by searching, but at least if you make any updates you’ll have a change history!)
Thank you! I’ll give it a try.
Hi~ o(￣▽￣)ブ，i have the same problem with you now.Do you have some effective methods to deal with it? Thank you very much!
Yes, the solution @ericaVoss provided is correct and worked well. Give it a try! If it doesn’t work for you, post some codes, code descriptions and the vocabulary of the code system here. I might be able to provide a solution! And if I don’t know, I’m sure one of our OHDSI friends will have some insight!