Concept marked as updated, but no 'Concept replaced by' relationship

Vocabulary Users
1

vocabulary_id updated_concepts replaced_bys
SPL 6 6
ATC 6 0
Domain 11 11
CPT4 92 0
MedDRA 21,036 21,033
UCUM 59 56
DRG 32 0
ETC 5 0
ICD10 266 266
LOINC 2,474 2,474
RxNorm 43,890 43,879
ICD9CM 1,108 1,108
SNOMED 27,336 23,269
HCPCS 113 68

SELECT vocabulary_id, count(*) as Updated_concepts
, SUM( CASE WHEN rel.relationship_id IS NOT NULL then 1 ELSE 0 END ) AS replaced_bys
FROM concept
LEFT OUTER JOIN concept_relationship rel
ON concept_id_1 = concept_id
AND relationship_id = ‘Concept replaced by’
WHERE concept.invalid_reason = ‘U’
GROUP BY vocabulary_id

2

@DTorok:

there is more than one relationship_id that designates replacements, depending on the vocabulary:

‘Maps to’,
‘UCUM replaced by’,
‘Concept replaced by’,
‘Concept same_as to’,
‘Concept alt_to to’,
‘Concept poss_eq to’,
‘Concept was_a to’,
‘LOINC replaced by’,
‘RxNorm replaced by’,
‘SNOMED replaced by’,
‘ICD9P replaced by’)

But even if you use those there are still a few that slip through. Need to add that check. Thanks for the vigilance.

And we want to consolidate all those relationship_ids, but that’s a bigger job. It’s in the WG.

3

Concept id marked as Update with no ‘Concept replaced by’ relationship.
vocabulary: OMOP Vocabulary v4.5 07-OCT-16 (do not know if error in creating v4 from v5)

This query assumes possible relationship from previous answer, but looks like all consolidated into ‘Concept Replace by’

select COALESCE( relationship_name, ‘No Relationship’ )
, count(*) updated_concept
, SUM( CASE WHEN cr.relationship_id IS NOT NULL THEN 1 ELSE 0 END ) AS relationship_defined
, SUM( CASE WHEN c2.concept_id IS NOT NULL THEN 1 ELSE 0 END ) AS replacement_defined
FROM concept c1
left outer JOIN concept_relationship cr ON cr.relationship_id IN( 1, 309, 313, 345, 347 ) AND concept_id_1 = c1.concept_id
LEFT OUTER JOIN relationship rel ON rel.relationship_id = cr.relationship_id
LEFT OUTER JOIN concept c2 ON c2.concept_id = concept_id_2
WHERE c1.invalid_reason = ‘U’
GROUP BY 1;

Relationship Name; Updated; Relationship Found, New Concept found
Concept replaced by; 71203; 71203; 71203
No Relationship ;64181; 0; 0

This seems especially prevalent for Read Code Mapping to SNOMED
where 7,868 of the SNOMED concepts pointed to are marked as updated, but only 38 have a matching ‘Concept replaced by’ relationship

4

@DTorok:

You forgot that there is more than one relationship that indicates an update of a deprecated concept:

SELECT * relationship
WHERE relationship_id IN (
                                  'Concept same_as to',
                                  'Concept alt_to to',
                                  'Concept poss_eq to',
                                  'Concept was_a to'
);

They are less strong than ‘Concept replaced by’ but that’s what we get from the sources.