inst/QueryLibrary/Aggregate/condition_occurrence/CO21.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

CO21: Distribution of age, stratified by condition

Description

This query is used to provide summary statistics for the age across all condition occurrence records stratified by condition (condition_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 condition occurrence. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id.

Query

WITH parms as (
select cid::integer as cid  from unnest(regexp_split_to_array( nullif($1::text, '')::text, '\s*,\s*')) as cid),
selected_conditions AS
  ( SELECT *
    FROM @cdm.condition_occurrence
    WHERE condition_concept_id
       IN  (select cid from parms)
  )

SELECT
  concept_name                                                           AS condition,
  ordered_data.condition_concept_id                                      AS condition_concept_id,
  count(*)::INTEGER                                                               AS condition_occurrences,
  min(age)::INTEGER                                                               AS min_age,
  round(avg(age), 2)::NUMERIC                                                     AS avg_age,
  max(age)::INTEGER                                                               AS max_age,
  round(STDDEV(age), 1)::NUMERIC                                                   AS STDEV_age,
  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,
  MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE age END) AS percentile_25

FROM
 ( SELECT
    condition_concept_id,
    cast((date_part('year',condition_start_date) - year_of_birth) as integer)                             AS age,
    ROW_NUMBER() OVER (PARTITION BY condition_concept_id ORDER BY (date_part('year',condition_start_date) - year_of_birth)) AS order_nr
   FROM selected_conditions
   INNER JOIN @cdm.person
     ON selected_conditions.person_id = person.person_id
 ) AS ordered_data
INNER JOIN
 ( SELECT
    condition_concept_id,
    COUNT(*)::integer AS population_size
  FROM selected_conditions
  GROUP BY condition_concept_id
 ) AS population_sizes
ON ordered_data.condition_concept_id = population_sizes.condition_concept_id
  JOIN @vocab.concept ON concept_id = population_sizes.condition_concept_id
GROUP BY concept_name, ordered_data.condition_concept_id
ORDER BY ordered_data.condition_concept_id DESC;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | condition_concept_id list |4099653, 4099741, 4102018, 4129378, 4129516| Yes | SNOMED condition concept identifiers for diabetes |

Output

| Field | Description | | --- | --- | | condition | Name of the condition | | condition_concept_id | Condition concept identifier | | min_age | Minimum age of the people with condition | | max_age | Maximum age of the people with condition | | avg_age | Average age of the people with condition | | STDEV_age | Standard deviation of the people with condition | | percentile_25 | Age 25th percentile of the people with condition | | median_age | Median age of the people with condition | | percentile_75 | Age 75th percentile of the people with condition |

Example output record

| Field | Description | | --- | --- | | condition | Type 1 diabetes mellitus | | condition_concept_id | 201826 | | min_age | 2006 | | max_age | 2017 | | avg_age | 2014 | | STDEV_age | 3.6 | | percentile_25 | 2009 | | median_age | 2013 | | percentile_75 | 2015 |

Documentation

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

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