OHDSI Home | Forums | Wiki | Github

ICD9 and ICD 10 Hierarchy

Developers
#1

We are trying to create a repository of codes using the Concept table, however we wanted to have the codes to be displayed as per the their hierarchy similar to what we have in ICD9data.com and ICD10data.com just wanted to reach out to the community to know if there are similar work or any thought process available/designed to implement such hierarchy.

Thanks
Ajinkya

#2

Ajinkya,

Something similar in the community is Athena where you may reach the concepts among ICD9/10 and some other vocabularies. On the concept personal page the hierarchy in text or graphic view is available.

BTW, to find the hierarchy of concepts in the CDM the concept_relationship & concept_ancestor tables is only choice.

#3

@Alexdavv Thanks for your suggestion, but what we are experiencing is, when we use concept_ancestor table for identifying the hierarchical relationships, we could find the relationships for all the vocabulary but ICD9/ICD10 and we were hoping to get some Grandparent > Parent> Child relationship using this table as per your suggestion. If we could get any help in identifying this hierarchical relationship, would be really helpful .

Thanks,
Ajinkya

#4

Hi @ Ajinkya_Patale
you may use concept_relationship table, relationship_id =‘Is a’. But it has all the way up relationships:
Child -> Parent
Child -> Grendparent, etc.
for example for ICD10CM concept S68.629D you’ll have all it’s ancestors.
http://athena.ohdsi.org/search-terms/terms/45545400
If you need closest relationships, you need to play around with concept codes as they are inherited, for example to find the closest parent of ‘S68.629D’ you need to find the longest match, it’s ‘S68.629’.
Let me know if you need any help with SQL

#5

Ah, anyway,
here’s your query

select  a.concept_code, coalesce (b.concept_code, c.concept_code, d.concept_code) 
from concept a
--there 1 to 3 symbols distance between the closest codes in ICD10CM
left join concept b  on regexp_replace ( a.concept_code, '.$', '')=  b.concept_code  and b.vocabulary_id  ='ICD10CM'
left join concept c  on regexp_replace ( a.concept_code, '..$', '')=  c.concept_code and c.vocabulary_id  ='ICD10CM'
left join concept d  on regexp_replace ( a.concept_code, '...$', '')=  d.concept_code  and d.vocabulary_id  ='ICD10CM' 
where a.vocabulary_id ='ICD10CM'  
and a.concept_code like 'A%' -- giving only one chapter in the example so it'll be faster
t