inst/QueryLibrary/Aggregate/general/G17.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

G17: Statistics about Drugs Mapping of Source Vocabularies

Description

The following query contains the coverage for mapped source vocabularies in the Drug domains to RxNorm.

Query

SELECT
  mapped.vocabulary_id                                                                                AS vocabulary_id,
  mapped.standard_concept                                                                             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_codes,
  (SELECT 
    COUNT(*)::integer
   FROM @vocab.concept
   WHERE vocabulary_id='RxNorm'
         AND standard_concept=mapped.standard_concept
         AND invalid_reason IS NULL
  )                                                                                                   AS standard_concepts,
  cast(ROUND( mapped.mapped_concepts*100/
         ( SELECT 
              CASE COUNT(*) 
                WHEN 0 THEN 1e16 
                ELSE COUNT(*) 
              END
           FROM @vocab.concept
           WHERE vocabulary_id='RxNorm'
                 AND standard_concept=mapped.standard_concept
                 AND invalid_reason IS NULL ) ,1)      as numeric)                                               AS pct_mapped_concepts
FROM 
( SELECT
    c1.vocabulary_id                    AS vocabulary_id,
    c2.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 IN ('ICD9CM','RxNorm') 
        AND c2.domain_id='Drug'
  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 | | standard_concepts | Number of standard concepts | | pct_mapped_concepts | Percentile of of mapped concepts |

Example output record

| Field | Value | | --- | --- | | vocabulary_id | NDC | | standard_concept | S | | mapped_codes | 537198 | | total_mapped_codes | 537198 | | pct_mapped_codes | 100.0 | | mapped_concepts | 29359 | | standard_concepts | 145747 | | pct_mapped_concepts | 20.1 |

Documentation

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

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