General Queries‎ > ‎

G17: Statistics about Drugs Mapping of Source Vocabularies

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

Input:
<None>

Sample query run:
The following query returns list of statistics.
SELECT 
  mapped.vocabulary_id,
  mapped.vocabulary_name,
  CASE mapped.concept_level WHEN 0 THEN 'Not mapped' ELSE to_char(mapped.concept_level, '9999') END AS concept_level,
  mapped.mapped_codes,
  sum(mapped.mapped_codes) over (partition by vocabulary_id) AS total_mapped_codes,
  to_char(mapped.mapped_codes*100/sum(mapped.mapped_codes) over (partition by vocabulary_id), '990.9') AS pct_mapped_codes,
  mapped.mapped_concepts,
  (SELECT count(8) FROM vocabulary.concept WHERE vocabulary_id=8 and concept_level=mapped.concept_level and invalid_reason IS NULL) 
    AS concepts_in_level,
  to_char(mapped.mapped_concepts*100/(
    SELECT CASE mapped.concept_level WHEN 0 THEN 1e16 ELSE count(8) END 
    FROM   vocabulary.concept 
    WHERE  vocabulary_id=8 AND concept_level=mapped.concept_level AND invalid_reason IS NULL
  ), '990.9') AS pct_mapped_concepts
FROM (
  SELECT 
    m.source_vocabulary_id AS vocabulary_id, 
    v.vocabulary_name,
    c.concept_level,
    count(8) AS mapped_codes,
    count(DISTINCT target_concept_id) AS mapped_concepts
  FROM vocabulary.source_to_concept_map m
  JOIN vocabulary.concept c ON c.concept_id=m.target_concept_id
  JOIN vocabulary.vocabulary v ON v.vocabulary_id=m.source_vocabulary_id
  WHERE m.target_vocabulary_id IN (8,22) AND lower(m.mapping_type)='drug' 
  GROUP BY m.source_vocabulary_id, v.vocabulary_name, c.concept_level
) mapped 
Output:
Output field list:
 Field  Description
 vocabulary_id  Source Vocabulary ID
 vocabulary_name  Source Vocabulary name
 concept_level  Concept Level Number
 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

Sample output record:
 Field  Value
 vocabulary_id  9
 vocabulary_name  NDC
 concept_level  1
 mapped_codes  550959
 total_mapped_codes  551757
 pct_mapped_codes  99.0
 mapped_concepts  33206
 concepts_in_level  57162
 pct_mapped_concepts  58.0
Comments