Phenotype Library

Exercise

Using the OHDSI phenotype library

1. Extract the Gastrointestinal bleeding with inpatient admission cohort definition.
2. Populate the Eunomia GiBleed cohort table with patients satisfying this definition.
3. View cohort diagnostics in RShiny App

Packages

HADES packages used

• Eunomia for connecting to a synthetic OMOP CDM database.
• PhenotypeLibrary for pulling OHDSI-vetted cohort definitions.
• CohortGenerator for generating cohort tables in Eunomia
• CohortDiagnostics for launching RShiny app to view cohort diagnostics.
• DatabaseConnector for connecting to our Eunomia dataset and executing queries

Other Packages used

• DT for viewing phenotype log in jquery datatables format.
• dplyr for piping and data manipulation functions.

Installation

# Needed to install some HADES packages
install.packages("remotes")

# Install HADES packages
install.packages("Eunomia")
install.packages("DatabaseConnector")
remotes::install_github("ohdsi/PhenotypeLibrary")
remotes::install_github("OHDSI/CohortGenerator")
remotes::install_github("OHDSI/CohortDiagnostics")

# Other supporting packages
install.packages("DT")
install.packages("dplyr")

library(dplyr)

Extract GI Bleed Cohort

Let’s begin by finding and extracting the GI Bleed cohort into an R variable.

View Phenotype Log

To start, let’s look at the phenotype log. This is a table of all the OHDSI-vetted phenotypes

The PhenotypeLog is a tibble.

class(PhenotypeLibrary::getPhenotypeLog())

[1] "tbl_df"     "tbl"        "data.frame"

It has a lot of columns, so we will just select cohortId and cohortName and pipe it into datatables for a more friendly view.

PhenotypeLibrary::getPhenotypeLog() %>% select(cohortId, cohortName) %>% DT::datatable()

An alternative display of the phenotype library, showing all columns.

PhenotypeLibrary::getPhenotypeLog() |> as.data.frame()

Find Gi Bleed Cohort

Using the search function of the DT table above, note that the cohortId for “Gastrointestinal bleeding with inpatient admission” is 77.

Let’s pull this cohort definition set data frame

cohort_ids <- c(77)
cohortDefinitionSet <- PhenotypeLibrary::getPlCohortDefinitionSet(cohortIds = cohort_ids)
cohortDefinitionSet

View Cohort Definition

json

The json column can be used to import into Atlas

json output

cohortDefinitionSet$json |> cat()

{
    "cdmVersionRange" : ">=5.0.0",
    "PrimaryCriteria" : {
        "CriteriaList" : [
            {
                "ConditionOccurrence" : {
                    "CodesetId" : 5,
                    "ConditionTypeExclude" : false
                }
            }
        ],
        "ObservationWindow" : {
            "PriorDays" : 0,
            "PostDays" : 0
        },
        "PrimaryCriteriaLimit" : {
            "Type" : "All"
        }
    },
    "AdditionalCriteria" : {
        "Type" : "ANY",
        "CriteriaList" : [
            {
                "Criteria" : {
                    "VisitOccurrence" : {
                        "CodesetId" : 2,
                        "VisitTypeExclude" : false
                    }
                },
                "StartWindow" : {
                    "Start" : {
                        "Coeff" : -1
                    },
                    "End" : {
                        "Days" : 0,
                        "Coeff" : 1
                    },
                    "UseIndexEnd" : false,
                    "UseEventEnd" : false
                },
                "EndWindow" : {
                    "Start" : {
                        "Days" : 0,
                        "Coeff" : -1
                    },
                    "End" : {
                        "Coeff" : 1
                    },
                    "UseIndexEnd" : false,
                    "UseEventEnd" : true
                },
                "RestrictVisit" : false,
                "IgnoreObservationPeriod" : false,
                "Occurrence" : {
                    "Type" : 2,
                    "Count" : 1,
                    "IsDistinct" : false
                }
            }
        ],
        "DemographicCriteriaList" : [],
        "Groups" : []
    },
    "ConceptSets" : [
        {
            "id" : 2,
            "name" : "Inpatient or ER visit",
            "expression" : {
                "items" : [
                    {
                        "concept" : {
                            "CONCEPT_ID" : 262,
                            "CONCEPT_NAME" : "Emergency Room and Inpatient Visit",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "ERIP",
                            "DOMAIN_ID" : "Visit",
                            "VOCABULARY_ID" : "Visit",
                            "CONCEPT_CLASS_ID" : "Visit"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 9203,
                            "CONCEPT_NAME" : "Emergency Room Visit",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "ER",
                            "DOMAIN_ID" : "Visit",
                            "VOCABULARY_ID" : "Visit",
                            "CONCEPT_CLASS_ID" : "Visit"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 9201,
                            "CONCEPT_NAME" : "Inpatient Visit",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "IP",
                            "DOMAIN_ID" : "Visit",
                            "VOCABULARY_ID" : "Visit",
                            "CONCEPT_CLASS_ID" : "Visit"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    }
                ]
            }
        },
        {
            "id" : 5,
            "name" : "Gastrointestinal hemorrhage GI bleeding",
            "expression" : {
                "items" : [
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4138962,
                            "CONCEPT_NAME" : "Acute duodenal ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "32490005",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4195231,
                            "CONCEPT_NAME" : "Acute gastric ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "67964002",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4147683,
                            "CONCEPT_NAME" : "Acute gastrojejunal ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "30514008",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : false,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4163865,
                            "CONCEPT_NAME" : "Acute peptic ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "45485004",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 195584,
                            "CONCEPT_NAME" : "Acute peptic ulcer without hemorrhage AND without perforation but with obstruction",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "58085004",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 40482685,
                            "CONCEPT_NAME" : "Angiodysplasia of duodenum",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "442267002",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 28779,
                            "CONCEPT_NAME" : "Bleeding esophageal varices",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "17709002",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4222896,
                            "CONCEPT_NAME" : "Chronic duodenal ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "40214005",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4296611,
                            "CONCEPT_NAME" : "Chronic gastric ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "76796008",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 200769,
                            "CONCEPT_NAME" : "Chronic gastric ulcer without hemorrhage, without perforation AND without obstruction",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "1567007",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4177387,
                            "CONCEPT_NAME" : "Chronic gastrojejunal ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "4269005",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 434400,
                            "CONCEPT_NAME" : "Chronic gastrojejunal ulcer without hemorrhage AND without perforation but with obstruction",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "56579005",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 438795,
                            "CONCEPT_NAME" : "Chronic gastrojejunal ulcer without hemorrhage, without perforation AND without obstruction",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "41626001",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4204555,
                            "CONCEPT_NAME" : "Chronic peptic ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "5492000",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 24973,
                            "CONCEPT_NAME" : "Chronic peptic ulcer without hemorrhage AND without perforation but with obstruction",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "12384004",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 23808,
                            "CONCEPT_NAME" : "Chronic peptic ulcer without hemorrhage, without perforation AND without obstruction",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "60400003",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 2002608,
                            "CONCEPT_NAME" : "Control of hemorrhage and suture of ulcer of stomach or duodenum",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "44.4",
                            "DOMAIN_ID" : "Procedure",
                            "VOCABULARY_ID" : "ICD9Proc",
                            "CONCEPT_CLASS_ID" : "3-dig nonbill code"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 198798,
                            "CONCEPT_NAME" : "Dieulafoy's vascular malformation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "109558001",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4198381,
                            "CONCEPT_NAME" : "Ulcer of duodenum",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "51868009",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4209746,
                            "CONCEPT_NAME" : "Duodenal ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "56776001",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4112183,
                            "CONCEPT_NAME" : "Esophageal varices with bleeding, associated with another disorder",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "195475003",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 2108900,
                            "CONCEPT_NAME" : "Esophagogastroduodenoscopy, flexible, transoral; with control of bleeding, any method",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "43255",
                            "DOMAIN_ID" : "Procedure",
                            "VOCABULARY_ID" : "CPT4",
                            "CONCEPT_CLASS_ID" : "CPT4"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 2108878,
                            "CONCEPT_NAME" : "Esophagoscopy, flexible, transoral; with control of bleeding, any method",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "43227",
                            "DOMAIN_ID" : "Procedure",
                            "VOCABULARY_ID" : "CPT4",
                            "CONCEPT_CLASS_ID" : "CPT4"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4265600,
                            "CONCEPT_NAME" : "Gastric ulcer",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "397825006",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4248429,
                            "CONCEPT_NAME" : "Gastric ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "73481001",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 192671,
                            "CONCEPT_NAME" : "Gastrointestinal hemorrhage",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "74474003",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4101104,
                            "CONCEPT_NAME" : "Gastrojejunal ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "2783007",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 443530,
                            "CONCEPT_NAME" : "Hematochezia",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "405729008",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 197925,
                            "CONCEPT_NAME" : "Hemorrhage of rectum and anus",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "266464001",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4027663,
                            "CONCEPT_NAME" : "Peptic ulcer",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "13200003",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : false,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    },
                    {
                        "concept" : {
                            "CONCEPT_ID" : 4291028,
                            "CONCEPT_NAME" : "Peptic ulcer without hemorrhage AND without perforation",
                            "STANDARD_CONCEPT" : "S",
                            "STANDARD_CONCEPT_CAPTION" : "Standard",
                            "INVALID_REASON" : "V",
                            "INVALID_REASON_CAPTION" : "Valid",
                            "CONCEPT_CODE" : "37442009",
                            "DOMAIN_ID" : "Condition",
                            "VOCABULARY_ID" : "SNOMED",
                            "CONCEPT_CLASS_ID" : "Clinical Finding"
                        },
                        "isExcluded" : true,
                        "includeDescendants" : true,
                        "includeMapped" : false
                    }
                ]
            }
        }
    ],
    "QualifiedLimit" : {
        "Type" : "All"
    },
    "ExpressionLimit" : {
        "Type" : "All"
    },
    "InclusionRules" : [],
    "EndStrategy" : {
        "DateOffset" : {
            "DateField" : "EndDate",
            "Offset" : 0
        }
    },
    "CensoringCriteria" : [],
    "CollapseSettings" : {
        "CollapseType" : "ERA",
        "EraPad" : 0
    },
    "CensorWindow" : {}
}

sql

You can also generate the sql if you prefer…

sql output

cohortDefinitionSet$sql |> cat()

CREATE TABLE #Codesets (
  codeset_id int NOT NULL,
  concept_id bigint NOT NULL
)
;

INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 2 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
( 
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (262,9203,9201)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (262,9203,9201)
  and c.invalid_reason is null

) I
) C UNION ALL 
SELECT 5 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
( 
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4147683,40482685,28779,2002608,198798,4198381,4112183,2108900,2108878,4265600,192671,4027663)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (40482685,28779,2002608,198798,4198381,4112183,2108900,2108878,4265600,192671,4027663)
  and c.invalid_reason is null

) I
LEFT JOIN
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4138962,4195231,4163865,195584,4222896,4296611,200769,4177387,434400,438795,4204555,24973,23808,4209746,4248429,4101104,443530,197925,4291028)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (4138962,4195231,4163865,195584,4222896,4296611,200769,4177387,434400,438795,4204555,24973,23808,4209746,4248429,4101104,443530,197925,4291028)
  and c.invalid_reason is null

) E ON I.concept_id = E.concept_id
WHERE E.concept_id is null
) C;

UPDATE STATISTICS #Codesets;


SELECT event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id
INTO #qualified_events
FROM 
(
  select pe.event_id, pe.person_id, pe.start_date, pe.end_date, pe.op_start_date, pe.op_end_date, row_number() over (partition by pe.person_id order by pe.start_date ASC) as ordinal, cast(pe.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM (-- Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
  select E.person_id, E.start_date, E.end_date,
         row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC, E.event_id) ordinal,
         OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM 
  (
  -- Begin Condition Occurrence Criteria
SELECT C.person_id, C.condition_occurrence_id as event_id, C.start_date, C.end_date,
  C.visit_occurrence_id, C.start_date as sort_date
FROM 
(
  SELECT co.person_id,co.condition_occurrence_id,co.condition_concept_id,co.visit_occurrence_id,co.condition_start_date as start_date, COALESCE(co.condition_end_date, DATEADD(day,1,co.condition_start_date)) as end_date 
  FROM @cdm_database_schema.CONDITION_OCCURRENCE co
  JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 5)
) C


-- End Condition Occurrence Criteria

  ) E
    JOIN @cdm_database_schema.observation_period OP on E.person_id = OP.person_id and E.start_date >=  OP.observation_period_start_date and E.start_date <= op.observation_period_end_date
  WHERE DATEADD(day,0,OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,0,E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
) P

-- End Primary Events
) pe
  
JOIN (
-- Begin Criteria Group
select 0 as index_id, person_id, event_id
FROM
(
  select E.person_id, E.event_id 
  FROM (-- Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
  select E.person_id, E.start_date, E.end_date,
         row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC, E.event_id) ordinal,
         OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM 
  (
  -- Begin Condition Occurrence Criteria
SELECT C.person_id, C.condition_occurrence_id as event_id, C.start_date, C.end_date,
  C.visit_occurrence_id, C.start_date as sort_date
FROM 
(
  SELECT co.person_id,co.condition_occurrence_id,co.condition_concept_id,co.visit_occurrence_id,co.condition_start_date as start_date, COALESCE(co.condition_end_date, DATEADD(day,1,co.condition_start_date)) as end_date 
  FROM @cdm_database_schema.CONDITION_OCCURRENCE co
  JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 5)
) C


-- End Condition Occurrence Criteria

  ) E
    JOIN @cdm_database_schema.observation_period OP on E.person_id = OP.person_id and E.start_date >=  OP.observation_period_start_date and E.start_date <= op.observation_period_end_date
  WHERE DATEADD(day,0,OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,0,E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
) P

-- End Primary Events
) E
  INNER JOIN
  (
    -- Begin Correlated Criteria
select 0 as index_id, cc.person_id, cc.event_id
from (SELECT p.person_id, p.event_id 
FROM (-- Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
  select E.person_id, E.start_date, E.end_date,
         row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC, E.event_id) ordinal,
         OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM 
  (
  -- Begin Condition Occurrence Criteria
SELECT C.person_id, C.condition_occurrence_id as event_id, C.start_date, C.end_date,
  C.visit_occurrence_id, C.start_date as sort_date
FROM 
(
  SELECT co.person_id,co.condition_occurrence_id,co.condition_concept_id,co.visit_occurrence_id,co.condition_start_date as start_date, COALESCE(co.condition_end_date, DATEADD(day,1,co.condition_start_date)) as end_date 
  FROM @cdm_database_schema.CONDITION_OCCURRENCE co
  JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 5)
) C


-- End Condition Occurrence Criteria

  ) E
    JOIN @cdm_database_schema.observation_period OP on E.person_id = OP.person_id and E.start_date >=  OP.observation_period_start_date and E.start_date <= op.observation_period_end_date
  WHERE DATEADD(day,0,OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,0,E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
) P

-- End Primary Events
) P
JOIN (
  -- Begin Visit Occurrence Criteria
select C.person_id, C.visit_occurrence_id as event_id, C.start_date, C.end_date,
       C.visit_occurrence_id, C.start_date as sort_date
from 
(
  select vo.person_id,vo.visit_occurrence_id,vo.visit_concept_id,vo.visit_start_date as start_date, vo.visit_end_date as end_date 
  FROM @cdm_database_schema.VISIT_OCCURRENCE vo
JOIN #Codesets cs on (vo.visit_concept_id = cs.concept_id and cs.codeset_id = 2)
) C


-- End Visit Occurrence Criteria

) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= DATEADD(day,0,P.START_DATE) AND A.END_DATE >= DATEADD(day,0,P.START_DATE) AND A.END_DATE <= P.OP_END_DATE ) cc 
GROUP BY cc.person_id, cc.event_id
HAVING COUNT(cc.event_id) >= 1
-- End Correlated Criteria

  ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
  GROUP BY E.person_id, E.event_id
  HAVING COUNT(index_id) > 0
) G
-- End Criteria Group
) AC on AC.person_id = pe.person_id and AC.event_id = pe.event_id

) QE

;

--- Inclusion Rule Inserts

create table #inclusion_events (inclusion_rule_id bigint,
    person_id bigint,
    event_id bigint
);

select event_id, person_id, start_date, end_date, op_start_date, op_end_date
into #included_events
FROM (
  SELECT event_id, person_id, start_date, end_date, op_start_date, op_end_date, row_number() over (partition by person_id order by start_date ASC) as ordinal
  from
  (
    select Q.event_id, Q.person_id, Q.start_date, Q.end_date, Q.op_start_date, Q.op_end_date, SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) as inclusion_rule_mask
    from #qualified_events Q
    LEFT JOIN #inclusion_events I on I.person_id = Q.person_id and I.event_id = Q.event_id
    GROUP BY Q.event_id, Q.person_id, Q.start_date, Q.end_date, Q.op_start_date, Q.op_end_date
  ) MG -- matching groups
{0 != 0}?{
  -- the matching group with all bits set ( POWER(2,# of inclusion rules) - 1 = inclusion_rule_mask
  WHERE (MG.inclusion_rule_mask = POWER(cast(2 as bigint),0)-1)
}
) Results

;

-- date offset strategy

select event_id, person_id, 
  case when DATEADD(day,0,end_date) > op_end_date then op_end_date else DATEADD(day,0,end_date) end as end_date
INTO #strategy_ends
from #included_events;


-- generate cohort periods into #final_cohort
select person_id, start_date, end_date
INTO #cohort_rows
from ( -- first_ends
    select F.person_id, F.start_date, F.end_date
    FROM (
      select I.event_id, I.person_id, I.start_date, CE.end_date, row_number() over (partition by I.person_id, I.event_id order by CE.end_date) as ordinal
      from #included_events I
      join ( -- cohort_ends
-- cohort exit dates
-- End Date Strategy
SELECT event_id, person_id, end_date from #strategy_ends

    ) CE on I.event_id = CE.event_id and I.person_id = CE.person_id and CE.end_date >= I.start_date
    ) F
    WHERE F.ordinal = 1
) FE;

select person_id, min(start_date) as start_date, end_date
into #final_cohort
from ( --cteEnds
    SELECT
         c.person_id
        , c.start_date
        , MIN(ed.end_date) AS end_date
    FROM #cohort_rows c
    JOIN ( -- cteEndDates
    SELECT
      person_id
      , DATEADD(day,-1 * 0, event_date)  as end_date
    FROM
    (
      SELECT
        person_id
        , event_date
        , event_type
        , SUM(event_type) OVER (PARTITION BY person_id ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS interval_status
      FROM
      (
        SELECT
          person_id
          , start_date AS event_date
          , -1 AS event_type
        FROM #cohort_rows

        UNION ALL

        SELECT
          person_id
          , DATEADD(day,0,end_date) as end_date
          , 1 AS event_type
        FROM #cohort_rows
      ) RAWDATA
    ) e
    WHERE interval_status = 0
  ) ed ON c.person_id = ed.person_id AND ed.end_date >= c.start_date
    GROUP BY c.person_id, c.start_date
) e
group by person_id, end_date
;

DELETE FROM @target_database_schema.@target_cohort_table where cohort_definition_id = @target_cohort_id;
INSERT INTO @target_database_schema.@target_cohort_table (cohort_definition_id, subject_id, cohort_start_date, cohort_end_date)
select @target_cohort_id as cohort_definition_id, person_id, start_date, end_date 
FROM #final_cohort CO
;

{1 != 0}?{
-- BEGIN: Censored Stats

delete from @results_database_schema.cohort_censor_stats where cohort_definition_id = @target_cohort_id;

-- END: Censored Stats
}
{1 != 0 & 0 != 0}?{

CREATE TABLE #inclusion_rules (rule_sequence int);

-- Find the event that is the 'best match' per person.  
-- the 'best match' is defined as the event that satisfies the most inclusion rules.
-- ties are solved by choosing the event that matches the earliest inclusion rule, and then earliest.

select q.person_id, q.event_id
into #best_events
from #qualified_events Q
join (
    SELECT R.person_id, R.event_id, ROW_NUMBER() OVER (PARTITION BY R.person_id ORDER BY R.rule_count DESC,R.min_rule_id ASC, R.start_date ASC) AS rank_value
    FROM (
        SELECT Q.person_id, Q.event_id, COALESCE(COUNT(DISTINCT I.inclusion_rule_id), 0) AS rule_count, COALESCE(MIN(I.inclusion_rule_id), 0) AS min_rule_id, Q.start_date
        FROM #qualified_events Q
        LEFT JOIN #inclusion_events I ON q.person_id = i.person_id AND q.event_id = i.event_id
        GROUP BY Q.person_id, Q.event_id, Q.start_date
    ) R
) ranked on Q.person_id = ranked.person_id and Q.event_id = ranked.event_id
WHERE ranked.rank_value = 1
;

-- modes of generation: (the same tables store the results for the different modes, identified by the mode_id column)
-- 0: all events
-- 1: best event


-- BEGIN: Inclusion Impact Analysis - event
-- calculte matching group counts
delete from @results_database_schema.cohort_inclusion_result where cohort_definition_id = @target_cohort_id and mode_id = 0;
insert into @results_database_schema.cohort_inclusion_result (cohort_definition_id, inclusion_rule_mask, person_count, mode_id)
select @target_cohort_id as cohort_definition_id, inclusion_rule_mask, count_big(*) as person_count, 0 as mode_id
from
(
  select Q.person_id, Q.event_id, CAST(SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) AS bigint) as inclusion_rule_mask
  from #qualified_events Q
  LEFT JOIN #inclusion_events I on q.person_id = i.person_id and q.event_id = i.event_id
  GROUP BY Q.person_id, Q.event_id
) MG -- matching groups
group by inclusion_rule_mask
;

-- calculate gain counts 
delete from @results_database_schema.cohort_inclusion_stats where cohort_definition_id = @target_cohort_id and mode_id = 0;
insert into @results_database_schema.cohort_inclusion_stats (cohort_definition_id, rule_sequence, person_count, gain_count, person_total, mode_id)
select @target_cohort_id as cohort_definition_id, ir.rule_sequence, coalesce(T.person_count, 0) as person_count, coalesce(SR.person_count, 0) gain_count, EventTotal.total, 0 as mode_id
from #inclusion_rules ir
left join
(
  select i.inclusion_rule_id, count_big(i.event_id) as person_count
  from #qualified_events Q
  JOIN #inclusion_events i on Q.person_id = I.person_id and Q.event_id = i.event_id
  group by i.inclusion_rule_id
) T on ir.rule_sequence = T.inclusion_rule_id
CROSS JOIN (select count(*) as total_rules from #inclusion_rules) RuleTotal
CROSS JOIN (select count_big(event_id) as total from #qualified_events) EventTotal
LEFT JOIN @results_database_schema.cohort_inclusion_result SR on SR.mode_id = 0 AND SR.cohort_definition_id = @target_cohort_id AND (POWER(cast(2 as bigint),RuleTotal.total_rules) - POWER(cast(2 as bigint),ir.rule_sequence) - 1) = SR.inclusion_rule_mask -- POWER(2,rule count) - POWER(2,rule sequence) - 1 is the mask for 'all except this rule'
;

-- calculate totals
delete from @results_database_schema.cohort_summary_stats where cohort_definition_id = @target_cohort_id and mode_id = 0;
insert into @results_database_schema.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)
select @target_cohort_id as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 0 as mode_id
FROM
(select count_big(event_id) as total from #qualified_events) PC,
(select sum(sr.person_count) as total
  from @results_database_schema.cohort_inclusion_result sr
  CROSS JOIN (select count(*) as total_rules from #inclusion_rules) RuleTotal
  where sr.mode_id = 0 and sr.cohort_definition_id = @target_cohort_id and sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
) FC
;

-- END: Inclusion Impact Analysis - event

-- BEGIN: Inclusion Impact Analysis - person
-- calculte matching group counts
delete from @results_database_schema.cohort_inclusion_result where cohort_definition_id = @target_cohort_id and mode_id = 1;
insert into @results_database_schema.cohort_inclusion_result (cohort_definition_id, inclusion_rule_mask, person_count, mode_id)
select @target_cohort_id as cohort_definition_id, inclusion_rule_mask, count_big(*) as person_count, 1 as mode_id
from
(
  select Q.person_id, Q.event_id, CAST(SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) AS bigint) as inclusion_rule_mask
  from #best_events Q
  LEFT JOIN #inclusion_events I on q.person_id = i.person_id and q.event_id = i.event_id
  GROUP BY Q.person_id, Q.event_id
) MG -- matching groups
group by inclusion_rule_mask
;

-- calculate gain counts 
delete from @results_database_schema.cohort_inclusion_stats where cohort_definition_id = @target_cohort_id and mode_id = 1;
insert into @results_database_schema.cohort_inclusion_stats (cohort_definition_id, rule_sequence, person_count, gain_count, person_total, mode_id)
select @target_cohort_id as cohort_definition_id, ir.rule_sequence, coalesce(T.person_count, 0) as person_count, coalesce(SR.person_count, 0) gain_count, EventTotal.total, 1 as mode_id
from #inclusion_rules ir
left join
(
  select i.inclusion_rule_id, count_big(i.event_id) as person_count
  from #best_events Q
  JOIN #inclusion_events i on Q.person_id = I.person_id and Q.event_id = i.event_id
  group by i.inclusion_rule_id
) T on ir.rule_sequence = T.inclusion_rule_id
CROSS JOIN (select count(*) as total_rules from #inclusion_rules) RuleTotal
CROSS JOIN (select count_big(event_id) as total from #best_events) EventTotal
LEFT JOIN @results_database_schema.cohort_inclusion_result SR on SR.mode_id = 1 AND SR.cohort_definition_id = @target_cohort_id AND (POWER(cast(2 as bigint),RuleTotal.total_rules) - POWER(cast(2 as bigint),ir.rule_sequence) - 1) = SR.inclusion_rule_mask -- POWER(2,rule count) - POWER(2,rule sequence) - 1 is the mask for 'all except this rule'
;

-- calculate totals
delete from @results_database_schema.cohort_summary_stats where cohort_definition_id = @target_cohort_id and mode_id = 1;
insert into @results_database_schema.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)
select @target_cohort_id as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 1 as mode_id
FROM
(select count_big(event_id) as total from #best_events) PC,
(select sum(sr.person_count) as total
  from @results_database_schema.cohort_inclusion_result sr
  CROSS JOIN (select count(*) as total_rules from #inclusion_rules) RuleTotal
  where sr.mode_id = 1 and sr.cohort_definition_id = @target_cohort_id and sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
) FC
;

-- END: Inclusion Impact Analysis - person

TRUNCATE TABLE #best_events;
DROP TABLE #best_events;

TRUNCATE TABLE #inclusion_rules;
DROP TABLE #inclusion_rules;
}

TRUNCATE TABLE #strategy_ends;
DROP TABLE #strategy_ends;


TRUNCATE TABLE #cohort_rows;
DROP TABLE #cohort_rows;

TRUNCATE TABLE #final_cohort;
DROP TABLE #final_cohort;

TRUNCATE TABLE #inclusion_events;
DROP TABLE #inclusion_events;

TRUNCATE TABLE #qualified_events;
DROP TABLE #qualified_events;

TRUNCATE TABLE #included_events;
DROP TABLE #included_events;

TRUNCATE TABLE #Codesets;
DROP TABLE #Codesets;

Populate Cohort Table

Now that we have the cohort definition, we need to import it into our OMOP CDM database.

Connect to Database

We will use the Eunomia GiBleed synthetic database for this tutorial, so let’s connect to it.

datasetName = "GiBleed"
dbms = "sqlite"

datasetLocation <- Eunomia::getDatabaseFile(
  datasetName = datasetName, 
  dbms = dbms, 
  databaseFile = tempfile(fileext = ".sqlite")
)
connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = dbms, server = datasetLocation)
connection = DatabaseConnector::connect(connectionDetails = connectionDetails)

Create Cohort Tables

New tables will be generated for cohort diagnostics/generation. Let’s have a look at them.

CohortGenerator::getCohortTableNames() |> str()

List of 7
 $ cohortTable               : chr "cohort"
 $ cohortSampleTable         : chr "cohort"
 $ cohortInclusionTable      : chr "cohort_inclusion"
 $ cohortInclusionResultTable: chr "cohort_inclusion_result"
 $ cohortInclusionStatsTable : chr "cohort_inclusion_stats"
 $ cohortSummaryStatsTable   : chr "cohort_summary_stats"
 $ cohortCensorStatsTable    : chr "cohort_censor_stats"

Let’s create the tables on our database

cdmDatabaseSchema <- "main"
cohortDatabaseSchema <- "main"
CohortGenerator::createCohortTables(
  connectionDetails = connectionDetails, 
  cohortDatabaseSchema = cohortDatabaseSchema
)

Connecting using SQLite driver
Creating cohort tables
- Created table main.cohort
- Created table main.cohort
- Created table main.cohort_inclusion
- Created table main.cohort_inclusion_result
- Created table main.cohort_inclusion_stats
- Created table main.cohort_summary_stats
- Created table main.cohort_censor_stats
Creating cohort tables took 0.08secs

Populate Cohort Tables

Recall, we used the GI Bleed cohort definition, and that is in cohortDefinitionSet.

Let’s now populate our newly generated cohort tables using that definition and our existing data from Eunomia GiBleed dataset.

cohortTables <- CohortGenerator::getCohortTableNames()
CohortGenerator::generateCohortSet(
  connectionDetails = connectionDetails,
  cdmDatabaseSchema = cdmDatabaseSchema,
  cohortDatabaseSchema = cohortDatabaseSchema,
  cohortTableNames = cohortTables,
  cohortDefinitionSet = cohortDefinitionSet
)

Let’s have a look at our main cohort table.

Notice:

• cohort_definition_id is 77, corresponding to the cohort 77 for GI Bleed
• We have subjects in and out of the cohort at the respective start/end dates, as per our definition of a cohort

sql <- "
select * from main.cohort limit 10;
"
DatabaseConnector::querySql(connection = connection, sql = sql)

View Cohort Diagnostics

Export Cohort Diagnostics

Export cohort diagnostics into csv files. This will be used later in an RShiny web-app for visualization

baseFolder = "./resources/artifacts/phenotype-library/"
exportFolder = file.path(baseFolder, "cohort-diagnostics")
cohortTable = "cohort" # Not sure why you would have it a different name?

# Wrapping in try/catch so it works in binder...
tryCatch({
  CohortDiagnostics::executeDiagnostics(
    cohortDefinitionSet,
    connectionDetails = connectionDetails,
    cohortTable = cohortTable,
    cohortDatabaseSchema = cohortDatabaseSchema,
    cdmDatabaseSchema = cdmDatabaseSchema,
    exportFolder = exportFolder,
    databaseId = "MyCdm",
    minCellCount = 5
  )
}, error = function(e) {})

Launch Cohort Diagnostics Explorer

Create a sqlite file from the CSV output above so it can be displayed in an RShiny web app

sqliteDbPath <- file.path(baseFolder, "cohort-diagnostics.sqlite")
CohortDiagnostics::createMergedResultsFile(
  dataFolder = exportFolder, 
  overwrite = TRUE,
  sqliteDbPath = sqliteDbPath
)

Launch the WebApp. Note, this works okay locally, not great in mybinder. Something with the java calls is throwing it off, not sure what, but let me know if you find out a fix for this.

CohortDiagnostics::launchDiagnosticsExplorer(sqliteDbPath = sqliteDbPath)

Debugging Stuff

Just playing around, testing things…

Get the table names of original connection

DatabaseConnector::getTableNames(connection)

Get a connection to the sqlite file used by the diagnostics viewer

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), "./resources/artifacts/phenotype-library/cohort-diagnostics.sqlite")

List tables in that sqlite file, just to see what they are. Probably match up with the csv files.

dbListTables(con)

Ensure you have data in one of the tables. It seems mybinder did not populate this, which resulted in an error. So, after having created the files, try refreshing the file explorer bane (bottom right). This may fix things. At least it fixes one error, but then you get that java error later on. Still working on it.

dbGetQuery(con, "SELECT * FROM database")