title: Is this an appropriate way to get all of the STIs in ICD9CM that map to the SNOMED categorization of STI?
body: <p>I have a data set that uses ICD9CM codes to encode condition/diagnosis.  I would like to use the SNOMED-CD category for &quot;Sexually transmitted infectious disease&quot; (concept_id = 8098009) using the OHDSI/OMOP/Athena concept tables.  To do this I am doing the following:</p>
  <p><strong>Get the SNOMED STI Concept:</strong></p>
  <pre><code>select * from omop_concepts.concept
  where
  concept_code = '8098009'
  ;
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;"></th>
  <th style="text-align: center;"></th>
  <th style="text-align: center;"></th>
  <th style="text-align: center;"></th>
  <th style="text-align: center;"></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">Clinical Finding</td>
  <td style="text-align: center;">8098009</td>
  <td style="text-align: center;">440647</td>
  <td style="text-align: center;">Sexually transmitted infectious disease</td>
  <td style="text-align: center;">Condition</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Get the Standard Concepts for each member of the category and store it in a table</strong></p>
  <pre><code>drop table if exists snomed_sti_standard_concept;
  
  create table snomed_sti_standard_concept as (
  select 
    root.concept_id root_concept_id,
    root.concept_name root_concept_name,
    c.concept_id concept_id,
    c.concept_name concept_name,
    c.standard_concept standard_concept,
    s.concept_id as standard_concept_id,
    s.concept_name as standard_concept_name,
    s.standard_concept as standard_standard_concept,
    root.domain_id
  from 
    omop_concepts.concept root
    join omop_concepts.concept_ancestor ca on 1=1
      and root.concept_id = ca.ancestor_concept_id
    left outer join omop_concepts.concept_relationship rel on 1=1
      and ca.descendant_concept_id = rel.concept_id_1
      and rel.relationship_id = 'Maps to'
    left outer join omop_concepts.concept c on 1=1
      and ca.descendant_concept_id = c.concept_id
    left outer join omop_concepts.concept s on 1=1
      and s.concept_id = rel.concept_id_2
      and s.standard_concept = 'S'
  where 
    root.concept_id = 440647
  );
  </code></pre>
  <p><strong>Get the ICD9CM Concepts that map to the Standard Concepts that define the SNOMED-CT STI category</strong></p>
  <pre><code>select
    sti.concept_id as snomed_categorization_id,
    sti.concept_name as snomed_categorization_name,
    c.*
  from
    snomed_sti_standard_concept sti
    join omop_concepts.concept_relationship rel on 1=1
      and sti.standard_concept_id = rel.concept_id_2
      and rel.relationship_id = 'Maps to'
    join omop_concepts.concept c on 1=1
      and c.concept_id = rel.concept_id_1
      and c.vocabulary_id = 'ICD9CM'
  order by 2
  ;
  </code></pre>
  <p>The output from this query (the list of ICD9CM codes that are mapped to the SNOMED STI category) are here: <a href="https://github.com/NACHC-CAD/public-examples/tree/main/ohdsi/queries/terminology/sti-example" rel="nofollow noreferrer">https://github.com/NACHC-CAD/public-examples/tree/main/ohdsi/queries/terminology/sti-example</a> .  The complete Databricks script used for this example is also available at that location.  The Databricks script assumes you have loaded the OMOP terminology tables to a schema called omop_concepts.</p>
  <p>There are 357 concepts that are descendants of the SNOMED STI concept used here.  There are 154 ICD9CM concepts that map to these descendants using the method described above.</p>