OHDSI Home | Forums | Wiki | Github

Custom SQL feature analysis - observation table

General
#1

Hello all!

I tried to write a custom SQL for feature analysis on characterization to capture observations within 60 days after cohort entry event, however when I execute the analysis I do not get any output. Could you tell me what I would need to change in the following code?

SELECT
  CAST(observation_concept_id AS BIGINT) * 1000 + @analysis_id AS covariate_id,
  c.concept_name AS covariate_name,
  observation_concept_id AS concept_id,
  COUNT(*) AS sum_value,
  COUNT(*) * 1.0 / stat.total_cnt * 1.0 AS average_value
FROM (
       SELECT DISTINCT
         observation_concept_id,
         cohort.subject_id,
         cohort.cohort_start_date
       FROM @cohort_table cohort
         INNER JOIN @cdm_database_schema.observation ON cohort.subject_id = observation.person_id
       WHERE observation_start_date <= dateadd(d,60,cohort.cohort_start_date)
             AND observation_concept_id != 0
             AND cohort.cohort_definition_id = @cohort_id
     ) observation_entries
  JOIN @cdm_database_schema.concept c ON observation_entries.observation_concept_id = c.concept_id
  CROSS JOIN (SELECT COUNT(*) total_cnt
              FROM @cohort_table
              WHERE cohort_definition_id = @cohort_id) stat
GROUP BY observation_concept_id, c.concept_name, stat.total_cnt

Thanks a lot!

t