Using the default set of covariates

Using the default set of covariates is straightforward:

settings <- createDefaultCovariateSettings()

This will create a wide array of features, ranging from demographics, through conditions and drugs, to several risk scores.

Note that we could specify a set of concepts that should not be used to create covariates, for example:

settings <- createDefaultCovariateSettings(
  excludedCovariateConceptIds = 1124300,
  addDescendantsToExclude = TRUE
)

This will create the default set of covariates, except those derived from concept 1124300 (the ingredient diclofenac) and any of its descendants (ie. all drugs containing the ingredient diclofenac).

Using prespecified analyses

The function createCovariateSettings allow the user to choose from a large set of predefined covariates. Type ?createCovariateSettings to get an overview of the available options. For example:

settings <- createCovariateSettings(
  useDemographicsGender = TRUE,
  useDemographicsAgeGroup = TRUE,
  useConditionOccurrenceAnyTimePrior = TRUE
)

This will create binary covariates for gender, age (in 5 year age groups), and each concept observed in the condition_occurrence table any time prior to (and including) the cohort start date.

Many of the prespecified analyses refer to a short, medium, or long term time window. By default, these windows are defined as:

• Long term: 365 days prior up to and including the cohort start date.
• Medium term: 180 days prior up to and including the cohort start date.
• Short term: 30 days prior up to and including the cohort start date.

However, the user can change these values. For example:

settings <- createCovariateSettings(
  useConditionEraLongTerm = TRUE,
  useConditionEraShortTerm = TRUE,
  useDrugEraLongTerm = TRUE,
  useDrugEraShortTerm = TRUE,
  longTermStartDays = -180,
  shortTermStartDays = -14,
  endDays = -1
)

This redefines the long term window as 180 days prior up to (but not including) the cohort start date, and redefines the short term window as 14 days prior up to (but not including) the cohort start date.

Again, we can also specify which concept IDs should or should not be used to construct covariates:

settings <- createCovariateSettings(
  useConditionEraLongTerm = TRUE,
  useConditionEraShortTerm = TRUE,
  useDrugEraLongTerm = TRUE,
  useDrugEraShortTerm = TRUE,
  longTermStartDays = -180,
  shortTermStartDays = -14,
  endDays = -1,
  excludedCovariateConceptIds = 1124300,
  addDescendantsToExclude = TRUE
)

Creating a set of custom covariates

This option should only be used by advanced users. It requires one to understand that at the implementation level, an analysis is a combination of a piece of highly parameterized SQL together with a specification of the parameter values. The best way to understand the available options is to take a prespecified analysis as starting point, and convert it to a detailed setting object:

settings <- createCovariateSettings(useConditionEraLongTerm = TRUE)
settings2 <- convertPrespecSettingsToDetailedSettings(settings)
settings2$analyses[[1]]

## $analysisId
## [1] 202
## 
## $sqlFileName
## [1] "DomainConcept.sql"
## 
## $parameters
## $parameters$analysisId
## [1] 202
## 
## $parameters$analysisName
## [1] "ConditionEraLongTerm"
## 
## $parameters$startDay
## [1] -365
## 
## $parameters$endDay
## [1] 0
## 
## $parameters$subType
## [1] "all"
## 
## $parameters$domainId
## [1] "Condition"
## 
## $parameters$domainTable
## [1] "condition_era"
## 
## $parameters$domainConceptId
## [1] "condition_concept_id"
## 
## $parameters$domainStartDate
## [1] "condition_era_start_date"
## 
## $parameters$domainEndDate
## [1] "condition_era_end_date"
## 
## $parameters$description
## [1] "One covariate per condition in the condition_era table overlapping with any part of the long term window."
## 
## 
## $includedCovariateConceptIds
## list()
## 
## $includedCovariateIds
## list()
## 
## $addDescendantsToInclude
## [1] FALSE
## 
## $excludedCovariateConceptIds
## list()
## 
## $addDescendantsToExclude
## [1] FALSE

One can create a detailed analysis settings object from scratch, and use it to create a detailed settings object:

analysisDetails <- createAnalysisDetails(
  analysisId = 1,
  sqlFileName = "DemographicsGender.sql",
  parameters = list(
    analysisId = 1,
    analysisName = "Gender",
    domainId = "Demographics"
  ),
  includedCovariateConceptIds = c(),
  addDescendantsToInclude = FALSE,
  excludedCovariateConceptIds = c(),
  addDescendantsToExclude = FALSE,
  includedCovariateIds = c()
)

settings <- createDetailedCovariateSettings(list(analysisDetails))

Temporal covariates

Ordinarily, covariates are created for just a few time windows of interest, for example the short, medium, and long term windows described earlier. However, sometimes a more fine-grained temporal resolution is required, for example creating covariates for each day separately, in the 365 days prior to cohort start. We will refer to this type of covariates as temporal covariates. Temporal covariates share the same covariate ID across the time windows, and use a separate time ID to distinguish between time windows. There currently aren’t many applications that are able to handle temporal covariates. For example, the CohortMethod package will break when provided with temporal covariates. However, there are some machine learning algorithms in the PatientLevelPrediction package that require temporal covariates.

Again, we can just choose to use the default settings:

settings <- createDefaultTemporalCovariateSettings()

Or, we can choose from a set of prespecified temporal covariates:

settings <- createTemporalCovariateSettings(
  useConditionOccurrence = TRUE,
  useMeasurementValue = TRUE
)

In this case we’ve chosen to create binary covariates for each concept in the condition_occurrence table, and continuous covariates for each measurement - unit combination in the measurement table in the CDM. By default, temporal covariates are created for each day separately in the 365 days before (but not including) the cohort start date. Different time windows can also be specified, for example creating 7 day intervals instead:

settings <- createTemporalCovariateSettings(
  useConditionOccurrence = TRUE,
  useMeasurementValue = TRUE,
  temporalStartDays = seq(-364, -7, by = 7),
  temporalEndDays = seq(-358, -1, by = 7)
)

Each time window includes the specified start and end day.

Similar to ordinary covariates, advanced users can also define custom analyses:

analysisDetails <- createAnalysisDetails(
  analysisId = 1,
  sqlFileName = "MeasurementValue.sql",
  parameters = list(
    analysisId = 1,
    analysisName = "MeasurementValue",
    domainId = "Measurement"
  ),
  includedCovariateConceptIds = c(),
  addDescendantsToInclude = FALSE,
  excludedCovariateConceptIds = c(),
  addDescendantsToExclude = FALSE,
  includedCovariateIds = c()
)

settings <- createDetailedTemporalCovariateSettings(list(analysisDetails))

Configuring the connection to the server

We need to tell R how to connect to the server where the data are. CohortMethod uses the DatabaseConnector package, which provides the createConnectionDetails function. Type ?createConnectionDetails for the specific settings required for the various database management systems (DBMS). For example, one might connect to a PostgreSQL database using this code:

connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost/ohdsi",
  user = "joe",
  password = "supersecret"
)

cdmDatabaseSchema <- "my_cdm_data"
resultsDatabaseSchema <- "my_results"

The last two lines define the cdmDatabaseSchema and resultSchema variables. We’ll use these later to tell R where the data in CDM format live, and where we want to write intermediate and result tables. Note that for Microsoft SQL Server, database schemas need to specify both the database and the schema, so for example cdmDatabaseSchema <- "my_cdm_data.dbo".

Creating a cohort of interest

FeatureExtraction requires the cohorts to be instantiated in the cohort table in the Common Data Model, or in a table that has the same structure as the cohort table. We could create cohorts using a cohort definition tool, but here we’ll just use some simple SQL to find the first drug era per person. Note that because we will be creating covariates based on data before cohort start, we are requiring 365 days of observation before the first exposure. FeatureExtraction will not check if a subject is observed during the specified time windows.

/***********************************
File cohortsOfInterest.sql
***********************************/

IF OBJECT_ID('@resultsDatabaseSchema.cohorts_of_interest', 'U') IS NOT NULL
DROP TABLE @resultsDatabaseSchema.cohorts_of_interest;

SELECT first_use.*
INTO @resultsDatabaseSchema.cohorts_of_interest
FROM (
SELECT drug_concept_id AS cohort_definition_id,
MIN(drug_era_start_date) AS cohort_start_date,
MIN(drug_era_end_date) AS cohort_end_date,
person_id 
FROM @cdmDatabaseSchema.drug_era
WHERE drug_concept_id = 1118084-- celecoxib
OR drug_concept_id = 1124300 --diclofenac
GROUP BY drug_concept_id, 
person_id
) first_use 
INNER JOIN @cdmDatabaseSchema.observation_period
ON first_use.person_id = observation_period.person_id
AND cohort_start_date >= observation_period_start_date
AND cohort_end_date <= observation_period_end_date
WHERE DATEDIFF(DAY, observation_period_start_date, cohort_start_date) >= 365;

This is parameterized SQL which can be used by the SqlRender package. We use parameterized SQL so we do not have to pre-specify the names of the CDM and result schemas. That way, if we want to run the SQL on a different schema, we only need to change the parameter values; we do not have to change the SQL code. By also making use of translation functionality in SqlRender, we can make sure the SQL code can be run in many different environments.

library(SqlRender)
sql <- readSql("cohortsOfInterest.sql")
sql <- render(sql,
  cdmDatabaseSchema = cdmDatabaseSchema,
  resultsDatabaseSchema = resultsDatabaseSchema
)
sql <- translate(sql, targetDialect = connectionDetails$dbms)

connection <- connect(connectionDetails)
executeSql(connection, sql)

In this code, we first read the SQL from the file into memory. In the next line, we replace the two parameter names with the actual values. We then translate the SQL into the dialect appropriate for the DBMS we already specified in the connectionDetails. Next, we connect to the server, and submit the rendered and translated SQL.

If all went well, we now have a table with the cohorts of interest. We can see how many events per type:

sql <- paste(
  "SELECT cohort_definition_id, COUNT(*) AS count",
  "FROM @resultsDatabaseSchema.cohorts_of_interest",
  "GROUP BY cohort_definition_id"
)
sql <- render(sql, resultsDatabaseSchema = resultsDatabaseSchema)
sql <- translate(sql, targetDialect = connectionDetails$dbms)

querySql(connection, sql)

##   cohort_concept_id  count
## 1           1124300 240761
## 2           1118084  47293

Creating per-person covariates for a cohort of interest

We can create per-person covariates for one of the cohorts of interest, for example using the default settings:

covariateSettings <- createDefaultCovariateSettings()

covariateData <- getDbCovariateData(
  connectionDetails = connectionDetails,
  cdmDatabaseSchema = cdmDatabaseSchema,
  cohortDatabaseSchema = resultsDatabaseSchema,
  cohortTable = "cohorts_of_interest",
  cohortIds = c(1118084),
  rowIdField = "subject_id",
  covariateSettings = covariateSettings
)
summary(covariateData)

covariateData$covariates

saveCovariateData(covariateData, "covariates")

tidyCovariates <- tidyCovariateData(covariateData,
  minFraction = 0.001,
  normalize = TRUE,
  removeRedundancy = TRUE
)

deletedCovariateIds <- attr(tidyCovariates, "metaData")$deletedInfrequentCovariateIds
head(deletedCovariateIds)

deletedCovariateIds <- attr(tidyCovariates, "metaData")$deletedRedundantCovariateIds
head(deletedCovariateIds)

library(Andromeda)
covariateData$covariateRef %>%
  filter(covariateId %in% deletedCovariateIds, ) %>%
  collect()

Creating aggregated covariates for a cohort of interest

Often we do not need to have per-person covariates, but instead we are interested in aggregated statistics instead. For example, we may not need to know which persons are male, but would like to know what proportion of the cohort is male. We can aggregate per-person covariates:

covariateData2 <- aggregateCovariates(covariateData)

Of course, if all we wanted was aggregated statistics it would have been more efficient to aggregate them during creation:

covariateSettings <- createDefaultCovariateSettings()

covariateData2 <- getDbCovariateData(
  connectionDetails = connectionDetails,
  cdmDatabaseSchema = cdmDatabaseSchema,
  cohortDatabaseSchema = resultsDatabaseSchema,
  cohortTable = "cohorts_of_interest",
  cohortIds = c(1118084),
  covariateSettings = covariateSettings,
  aggregated = TRUE
)
summary(covariateData2)

Note that we specified aggregated = TRUE. Also, we are no longer required to define a rowIdField because we will no longer receive per-person data.

covariateData2$covariates

covariateData2$covariatesContinuous

Creating a table 1

One task supported by the FeatureExtraction package is creating a table of overall study population characteristics that can be include in a paper. Since this is typically the first table in a paper we refer to such a table as ‘table 1’. A default table 1 is available in the FeatureExtraction package:

result <- createTable1(
  covariateData1 = covariateData2
)
print(result, row.names = FALSE, right = FALSE)

Where applicable, these characteristics are drawn from analyses pertaining the ‘long-term’ windows, so concepts observed in the 365 days before up to and included the cohort start date.

The createTable1 function requires a simple specification of what variables to include in the table. The default specifications included in the package can be reviewed by calling the getDefaultTable1Specifications function. The specification reference analysis IDs and covariate IDs, and in the default specification these IDs refer to those in the default covariate settings. It is possible to create custom table 1 specifications and use those instead.

Here we based table 1 on a covariateData object containing all default covariates, even though only a small fraction of covariates are used in the table. If we only want to extract those covariates needed for the table, we can use the createTable1CovariateSettings function:

covariateSettings <- createTable1CovariateSettings()

covariateData2b <- getDbCovariateData(
  connectionDetails = connectionDetails,
  cdmDatabaseSchema = cdmDatabaseSchema,
  cohortDatabaseSchema = resultsDatabaseSchema,
  cohortTable = "cohorts_of_interest",
  cohortIds = c(1118084),
  covariateSettings = covariateSettings,
  aggregated = TRUE
)
summary(covariateData2b)