OHDSI Home | Forums | Wiki | Github

Identify Leaf Level Condition Concepts Using SQL

Implementers
#1

Hello,

I am trying to identify the leaf level concepts in the condition domain using a sql query in the OMOP model if possible. The concept_ancestor table does not help because it does not explicitly state if a concept is leaf level. It is just relative to the concept you are querying for. Has anybody had any success identifying the leaf levels in the condition SNOMED hierarchy using the OMOP tables or did you have to go a different route?

#2

Lisa:

Sure you can. Just query for concepts who have no records in the CONCEPT_ANCESTOR table other than a record linking to itself:

select concept_id, concept_name
from concept
where domain_id='Condition' and vocabulary_id='SNOMED' and invalid_reason is null
and not exists (
  select 1 from concept_ancestor where ancestor_concept_id=concept_id and ancestor_concept_id!=descendant_concept_id
)
#3

Thank you! We were close just needed that second filter!

t