inst/QueryLibrary/Aggregate/drug_exposure/DEX03.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

DEX03: Distribution of age, stratified by drug

Description

| This query is used to provide summary statistics for the age across all drug exposure records stratified by drug (drug_concept_id): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The age value is defined by the earliest exposure. The input to the query is a value (or a comma-separated list of values) of a drug_concept_id. See vocabulary queries for obtaining valid drug_concept_id values. If the input is omitted, age is summarized for all existing drug_concept_id values.

Query

The following is a sample run of the query. The input parameters are highlighted in blue.

WITH first_exposure AS
  ( SELECT person_id, drug_concept_id, min(drug_exposure_start_date) as drug_exposure_start_date
    FROM @cdm.drug_exposure
    WHERE drug_concept_id
       IN  /*crestor 20 and 40 mg tablets */
                    (40165254, 40165258)
    GROUP BY person_id, drug_concept_id                
  )
SELECT
  concept_name                                                           AS drug,
  ordered_data.drug_concept_id                                           AS drug_concept_id,
  COUNT(*)                                                               AS patient_count,
  MIN(age)                                                               AS min,
  MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE age END) AS percentile_25,
  round(avg(age), 2)                                                     AS mean,
  MIN(CASE WHEN order_nr < .50 * population_size THEN 9999 ELSE age END) AS median,
  MIN(CASE WHEN order_nr < .75 * population_size THEN 9999 ELSE age END) AS percentile_75,
  max(age)                                                               AS max,
  round(STDEV(age), 1)                                                   AS STDEV
FROM
 ( SELECT
    drug_concept_id,
    YEAR(drug_exposure_start_date) - year_of_birth                                                                  AS age,
    ROW_NUMBER() OVER (PARTITION BY drug_concept_id ORDER BY (YEAR(drug_exposure_start_date) - year_of_birth)) AS order_nr
   FROM first_exposure
   INNER JOIN @cdm.person
     ON first_exposure.person_id = person.person_id
 ) AS ordered_data
INNER JOIN
 ( SELECT
    drug_concept_id,
    COUNT(*) AS population_size
  FROM first_exposure
  GROUP BY drug_concept_id
 ) AS population_sizes
ON ordered_data.drug_concept_id = population_sizes.drug_concept_id
  JOIN @vocab.concept ON concept_id = population_sizes.drug_concept_id
GROUP BY concept_name, ordered_data.drug_concept_id
ORDER BY ordered_data.drug_concept_id DESC;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | drug_concept_id | 40165254, 40165258 | Yes | Crestor 20 and 40 mg tablets |

Output

| Field | Description | | --- | --- | | drug_name | An unambiguous, meaningful and descriptive name for the concept. | | drug_concept_id | A foreign key that refers to a standard concept identifier in the vocabulary for the drug concept. | | patient_count | The count of patients taking the drug | | min | The age of the youngest patient taking the drug | | percentile_25 | The 25th age percentile | | mean | The mean or average age of the patients taking the drug | | median | The median age of the patients taking the drug | | percentile_75 | The 75th age percentile | | max | The age of the oldest patient taking the drug | | stddev | The standard deviation of the age distribution |

Example output record

| Field | Content | | --- | --- | | drug_name | Rosuvastatin calcium 20 MG Oral Tablet [Crestor] | | drug_concept_id | 40165254 | | patient_count | 30321 | | min | 11 | | percentile_25 | 49 | | mean | 53.87 | | median | 55 | | percentile_75 | 60 | | max | 93 | | stddev | 8.8 |

Documentation

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

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