inst/QueryLibrary/Aggregate/general/G16.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

G16: Statistics about Condition Mapping of Source Vocabularies

Description

The following query contains the coverage for mapped source vocabularies in the Condition domains to SNOMED-CT.

Query

SELECT
  mapped.vocabulary_id                                                                                  AS vocabulary_id,
  CASE mapped.standard_concept
    WHEN NULL THEN 'Not mapped'
    ELSE mapped.standard_concept
  END                                                                                                   AS standard_concept,
  mapped.mapped_codes                                                                                   AS mapped_codes,
  cast(sum(mapped.mapped_codes) OVER (PARTITION BY vocabulary_id)  as integer)                          AS total_mapped_codes,
  CAST(mapped.mapped_codes*100/sum(mapped.mapped_codes) OVER (PARTITION BY vocabulary_id) AS varchar)   AS pct_mapped_codes,
  mapped.mapped_concepts                                                                                AS mapped_concepts,
  ( SELECT 
      COUNT(*)::integer
    FROM @vocab.concept
    WHERE vocabulary_id='SNOMED' AND
         standard_concept=mapped.standard_concept AND
         lower(concept_class_id)='clinical finding' AND
         invalid_reason IS NULL
  )                                                                                                     AS standard_concepts,
  CAST(mapped.mapped_concepts*100/
         ( SELECT CASE COUNT(*) WHEN 0 THEN 1e16 ELSE COUNT(*)::integer END
                  FROM @vocab.concept
                  WHERE vocabulary_id='SNOMED' AND
                        standard_concept=mapped.standard_concept AND
                        lower(concept_class_id)='clinical finding' AND
                        invalid_reason IS NULL 
          ) AS VARCHAR)                                                                                 AS pct_mapped_concepts
FROM 
  ( SELECT
        c1.vocabulary_id              AS vocabulary_id,
        c2.standard_concept           AS standard_concept,
        COUNT(*)::integer                      AS mapped_codes,
        COUNT(DISTINCT c2.concept_id)::integer AS mapped_concepts
    FROM @vocab.concept_relationship AS m
    JOIN @vocab.concept AS c1 
      ON m.concept_id_1=c1.concept_id 
         AND m.relationship_id='Maps to' 
         AND m.invalid_reason IS NULL
    JOIN @vocab.concept AS c2 
      ON c2.concept_id=m.concept_id_2
    WHERE c2.vocabulary_id='SNOMED' 
          AND LOWER(c2.domain_id)='condition'
    GROUP BY c1.vocabulary_id, c2.standard_concept
  ) AS mapped;

Input

None

Output

| Field | Description | | --- | --- | | vocabulary_id | Source Vocabulary ID | | standard_concept | 'S' for standard, 'C' for classification or empty if non-standard | | mapped_codes | Number of mapped codes | | total_mapped_codes | Total number of mapped codes for source vocabulary | | pct_mapped_codes | Percentile of mapped code | | mapped_concepts | Number of mapped concepts | | concepts_in_level | Number of mapped concepts | | pct_mapped_concepts | Percentile of of mapped concepts |

Example output record

| Field | Value | | --- | --- | | vocabulary_id | ICD9CM | | standard_concept | S | | mapped_codes | 15613 | | total_mapped_codes | 15613 | | pct_mapped_codes | 100.0 | | mapped_concepts | 10087 | | concepts_in_level | 118433 | | pct_mapped_concepts | 8.5 |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/CONCEPT_RELATIONSHIP

sib-swiss/dsQueryLibraryServer documentation built on Feb. 13, 2025, 8:07 p.m.