inst/shinyApps/QueryLibrary/queries/condition_era/CE04.md

In OHDSI/QueryLibrary: A Query Library for the OMOP-CDM

CE04: Conditions, stratified by year, age group and gender

Description

This query is used to count conditions (condition_concept_id) across all condition era records stratified by year, age group and gender (gender_concept_id). The age groups are calculated as 10 year age bands from the age of a person at the condition era start date. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id , year, age_group (10 year age band) and gender_concept_id. If the input is ommitted, all existing value combinations are summarized..

Query

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

WITH age_age_grp (age, age_grp) AS (
SELECT 1, '0 to 9' UNION ALL
SELECT 2, '0 to 9' UNION ALL
SELECT 3, '0 to 9' UNION ALL
SELECT 4, '0 to 9' UNION ALL
SELECT 5, '0 to 9' UNION ALL
SELECT 6, '0 to 9' UNION ALL
SELECT 7, '0 to 9' UNION ALL
SELECT 8, '0 to 9' UNION ALL
SELECT 9, '0 to 9' UNION ALL
SELECT 10, '10 to 19' UNION ALL
SELECT 11, '10 to 19' UNION ALL
SELECT 12, '10 to 19' UNION ALL
SELECT 13, '10 to 19' UNION ALL
SELECT 14, '10 to 19' UNION ALL
SELECT 15, '10 to 19' UNION ALL
SELECT 16, '10 to 19' UNION ALL
SELECT 17, '10 to 19' UNION ALL
SELECT 18, '10 to 19' UNION ALL
SELECT 19, '10 to 19' UNION ALL
SELECT 20, '20 to 29' UNION ALL
SELECT 21, '20 to 29' UNION ALL
SELECT 22, '20 to 29' UNION ALL
SELECT 23, '20 to 29' UNION ALL
SELECT 24, '20 to 29' UNION ALL
SELECT 25, '20 to 29' UNION ALL
SELECT 26, '20 to 29' UNION ALL
SELECT 27, '20 to 29' UNION ALL
SELECT 28, '20 to 29' UNION ALL
SELECT 29, '20 to 29' UNION ALL
SELECT 30, '30 to 39' UNION ALL
SELECT 31, '30 to 39' UNION ALL
SELECT 32, '30 to 39' UNION ALL
SELECT 33, '30 to 39' UNION ALL
SELECT 34, '30 to 39' UNION ALL
SELECT 35, '30 to 39' UNION ALL
SELECT 36, '30 to 39' UNION ALL
SELECT 37, '30 to 39' UNION ALL
SELECT 38, '30 to 39' UNION ALL
SELECT 39, '30 to 39' UNION ALL
SELECT 40, '40 to 49' UNION ALL
SELECT 41, '40 to 49' UNION ALL
SELECT 42, '40 to 49' UNION ALL
SELECT 43, '40 to 49' UNION ALL
SELECT 44, '40 to 49' UNION ALL
SELECT 45, '40 to 49' UNION ALL
SELECT 46, '40 to 49' UNION ALL
SELECT 47, '40 to 49' UNION ALL
SELECT 48, '40 to 49' UNION ALL
SELECT 49, '40 to 49' UNION ALL
SELECT 50, '50 to 59' UNION ALL
SELECT 51, '50 to 59' UNION ALL
SELECT 52, '50 to 59' UNION ALL
SELECT 53, '50 to 59' UNION ALL
SELECT 54, '50 to 59' UNION ALL
SELECT 55, '50 to 59' UNION ALL
SELECT 56, '50 to 59' UNION ALL
SELECT 57, '50 to 59' UNION ALL
SELECT 58, '50 to 59' UNION ALL
SELECT 59, '50 to 59' UNION ALL
SELECT 60, '60 to 69' UNION ALL
SELECT 61, '60 to 69' UNION ALL
SELECT 62, '60 to 69' UNION ALL
SELECT 63, '60 to 69' UNION ALL
SELECT 64, '60 to 69' UNION ALL
SELECT 65, '60 to 69' UNION ALL
SELECT 66, '60 to 69' UNION ALL
SELECT 67, '60 to 69' UNION ALL
SELECT 68, '60 to 69' UNION ALL
SELECT 69, '60 to 69' UNION ALL
SELECT 70, '70 to 79' UNION ALL
SELECT 71, '70 to 79' UNION ALL
SELECT 72, '70 to 79' UNION ALL
SELECT 73, '70 to 79' UNION ALL
SELECT 74, '70 to 79' UNION ALL
SELECT 75, '70 to 79' UNION ALL
SELECT 76, '70 to 79' UNION ALL
SELECT 77, '70 to 79' UNION ALL
SELECT 78, '70 to 79' UNION ALL
SELECT 79, '70 to 79' UNION ALL
SELECT 80, '80 to 89' UNION ALL
SELECT 81, '80 to 89' UNION ALL
SELECT 82, '80 to 89' UNION ALL
SELECT 83, '80 to 89' UNION ALL
SELECT 84, '80 to 89' UNION ALL
SELECT 85, '80 to 89' UNION ALL
SELECT 86, '80 to 89' UNION ALL
SELECT 87, '80 to 89' UNION ALL
SELECT 88, '80 to 89' UNION ALL
SELECT 89, '80 to 89' UNION ALL
SELECT 90, '90 to 99' UNION ALL
SELECT 91, '90 to 99' UNION ALL
SELECT 92, '90 to 99' UNION ALL
SELECT 93, '90 to 99' UNION ALL
SELECT 94, '90 to 99' UNION ALL
SELECT 95, '90 to 99' UNION ALL
SELECT 96, '90 to 99' UNION ALL
SELECT 97, '90 to 99' UNION ALL
SELECT 98, '90 to 99' UNION ALL
SELECT 99, '90 to 99' UNION ALL
SELECT 100, '100 to 109' UNION ALL
SELECT 101, '100 to 109' UNION ALL
SELECT 102, '100 to 109' UNION ALL
SELECT 103, '100 to 109' UNION ALL
SELECT 104, '100 to 109' UNION ALL
SELECT 105, '100 to 109' UNION ALL
SELECT 106, '100 to 109' UNION ALL
SELECT 107, '100 to 109' UNION ALL
SELECT 108, '100 to 109' UNION ALL
SELECT 109, '100 to 109' UNION ALL
SELECT 110, '110 to 119' UNION ALL
SELECT 111, '110 to 119' UNION ALL
SELECT 112, '110 to 119' UNION ALL
SELECT 113, '110 to 119' UNION ALL
SELECT 114, '110 to 119' UNION ALL
SELECT 115, '110 to 119' UNION ALL
SELECT 116, '110 to 119' UNION ALL
SELECT 117, '110 to 119' UNION ALL
SELECT 118, '110 to 119' UNION ALL
SELECT 119, '110 to 119'
)
SELECT condition,
       year,
       age_group,
       gender,
       COUNT(*) AS count_value
  FROM (
SELECT p.person_id,
       c1.concept_name AS condition,
       YEAR(ce.condition_era_start_date ) AS year,
       c2.concept_name AS gender,
       YEAR(ce.condition_era_start_date ) - p.year_of_birth AS age,
       aag.age_grp AS age_group
  FROM @cdm.condition_era ce
  JOIN @vocab.concept c1
    ON c1.concept_id = ce.condition_concept_id
  JOIN @cdm.person p
    ON p.person_id = ce.person_id
  JOIN @vocab.concept c2
    ON c2.concept_id = p.gender_concept_id
  JOIN age_age_grp aag
    ON aag.age = YEAR(ce.condition_era_start_date ) - p.year_of_birth
       ) TMP
 GROUP BY condition,year,age_group,gender
 ORDER BY condition,year,age_group,gender;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | age_group | '50 to 59' | Yes | see the list of age groups |

Output

| Field | Description | | --- | --- | | condition | The condition | | year | The year for which the condition is counted | | age_group | The age group for which the condition is counted | | gender | The gender for which the condition is counted | | count_value | The number of occurrences of the condition for the specified year, age group, and gender |

Example output record

| Field | Description | | --- | --- | | condition | Alcoholic liver damage | | year | 2008 | | age_group | 50 to 59 | | gender | MALE | | count_value | 3 |

Documentation

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

OHDSI/QueryLibrary documentation built on Aug. 1, 2023, 5:42 a.m.