inst/shinyApps/QueryLibrary/queries/obervation_period/OP12.md

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

OP12: Distribution of observation period length

Description

This query is used to provide summary statistics for the observation period length across all observation period records: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The length of an is defined as the difference between the start date and the end date. No input is required for this query.

Query

WITH w AS 
  (SELECT
    DATEDIFF(day, DATEADD(DAY,1,observation_period_start_date), observation_period_end_date)    AS period_length
  FROM @cdm.observation_period
  ) 
SELECT 
  MIN( period_length )                                                              AS min_periods,
  MAX( period_length )                                                              AS max_periods,
  round( avg( period_length ) , 2 )                                                 AS avg_period,
  round( STDEV( period_length ) , 1 )                                               AS STDEV_period,
  MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE period_length END)  AS percentile_25,
  MIN(CASE WHEN order_nr < .50 * population_size THEN 9999 ELSE period_length END)  AS median,
  MIN(CASE WHEN order_nr < .75 * population_size THEN 9999 ELSE period_length END)  AS percentile_75
FROM 
 ( SELECT 
     period_length                                                     AS period_length,
     ROW_NUMBER() OVER (ORDER BY period_length)                        AS order_nr,
     (SELECT COUNT(*) FROM w )                                    AS population_size
   FROM w
 ) ordered_data;

Input

None

Output

| Field | Description | | --- | --- | | min_period | Minimum observation period duration in days | | max_period | Maximum observation period duration in days | | avg_period | Average observation period in days | | STDEV_period | Standard deviation of observation period days | | percentile_25 | 25th percentile of observation period days | | median | Median value of of observation period | | percentile_75 | 25th percentile of observation period days |

Example output record

| Field | Value | | --- | --- | | min_period | 1 | | max_period | 2372 | | avg_period | 655.91 | | STDEV_period | 501 | | percentile_25 | 365 | | median | 487 | | percentile_75 | 731 |

Documentation

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

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