I want a list of SNOMED and ultimately ICD9&10 codes that indicate a person died. I hoped to use the concept_ancestor table and get all the descendants for concept id 4306655 ‘Death’. The descendant codes look to be a comprehensive list of concepts indicating that a person died, but also include codes indicating death for someone/thing other than the person. Example, Death of relative, Death of Pet. Are concepts for a death other than the person incorrectly included as descendants?
That hierarchy is in the Observation domain which I believe include psychological observations. Would concept id 434489 from the Conditions domain better suit your purpose? I’m fairly new to this, so I defer to anyone else’s opinion.
@WustlRob you are right.
If you take a look at the descendants for 434489 ‘Dead’ you’ll find only the concepts that are related to a patient’s death.
Thank you for responses, but ‘Dead’ does not work for me. As I said, I wanted the ICD9/10 code to show in ETL specification I am working on. If I start with ancestor 434489 ‘Dead’ follow descendants to SNOMED and then go from SNOMED codes to ICD9/10 using relationship_id ‘Mapped from’ I only see 1 ICD9 code. Same code starting with ‘Death’ returns over 160 codes, but as noted some do not indicate that the person died.
Hi, @DTorok
can this help?
select * from concept where
vocabulary_id in (‘ICD10CM’, ‘ICD9CM’) and regexp_like ( lower (concept_name), ‘death|dead’)
and invalid_reason is null
minus
select * from concept where
vocabulary_id in (‘ICD10CM’, ‘ICD9CM’) and regexp_like ( lower (concept_name) ,‘family|continuing pregnancy|fetal|intrauterine’)
and invalid_reason is null
1 Like
That worked much better. Thanks