aou_survey – allofus

Function to query allofus observation table for survey responses

Description

Extracts survey responses in a tidy format that also includes ‘skip’ responses and collapses across all versions of the person health / personal medical history surveys. Currently responses in the ‘ds_survey’ table omit skipped responses. Responses are returned as Yes" if the respondent answered that the individual had the condition, No" if the respondent answered that the individual did not have that condition (or omitted it when selecting from related conditions), a skip response if the question was skipped, and NA if the respondent did not answer the question. Returns a data frame or SQL tbl with the initial cohort table along with a column for each question included in questions and answers foreach person_id in the cells. To find the desired survey questions, use the all of us data dictionary, survey codebook, Athena, data browser, or the modified codebook which can be found in the allofus R package.

Usage

aou_survey(
  cohort = NULL,
  questions,
  question_output = "concept_code",
  clean_answers = TRUE,
  collect = FALSE,
  ...,
  con = getOption("aou.default.con")
)

Arguments

cohort Reference to a remote table or local dataframe with a column called "person_id"

questions either a vector of concept_ids or concept_codes for questions to return results

question_output how to name the columns. Options include as the text of the concept code ("concept_code"), as concept ids preceded by "x_" ("concept_id"), or using a custom vector of column names matching the vector of questions. Defaults to "concept_code".

clean_answers whether to clean the answers to the survey questions. Defaults to TRUE.

collect Whether to bring the resulting table into local memory (collect = TRUE) as a dataframe or leave as a reference to a database table (for continued analysis using, e.g., dbplyr). Defaults to FALSE.

… additional arguments passed to collect() when collect = TRUE

con connection to the allofus SQL database. Defaults to getOption("aou.default.con"), which is created automatically with aou_connect()

Details

The function will return a dataframe or SQL tbl with the initial cohort table along with a column for each question included in questions and answers for each person_id in the cells. The column names (questions) can be returned as the concept_code or concept_id or by providing new column names. For each question, a column with the suffix "_date" is included with the date on which the question was answered. When questions can have multiple answers ("checkbox"-style questions), answers are returned as a comma-separated string.

To find the desired survey questions, use the all of us data dictionary, survey codebook, athena, data browser, or the allofus R package modified codebook which can be found here: https://roux-ohdsi.github.io/allofus/vignettes/searchable_codebook.html For questions regarding an individual’s health history or family health history, the function requires the specific concept_id (or concept_code) for individual in question, whether that is "self" or another relative. Responses are returned as "Yes" if the respondent answered that the individual had the condition, "No" if the respondent answered that the individual did not have that condition (or omitted it when selecting from related conditions), a skip response if the question was skipped, and NA if the respondent did not answer the question.

Value

A dataframe if collect = TRUE; a reference to a remote database table if not.

Examples

library("allofus")



con <- aou_connect()
cohort <- dplyr::tbl(con, "person") %>%
  dplyr::filter(person_id > 5000000) %>%
  dplyr::select(person_id, year_of_birth, gender_concept_id)

aou_survey(
  cohort,
  questions = c(1585375, 1586135),
  question_output = "concept_code"
)

# Source:   SQL [?? x 5]
# Database: BigQueryConnection
   person_id Income_AnnualIncome TheBasics_Birthplace Income_AnnualIncome_date
     <int64> <chr>               <chr>                <date>                  
 1   7468592 Skip                USA                  2024-11-13              
 2   5577162 25k35k              USA                  2023-07-25              
 3   9966371 Skip                Other                2022-12-22              
 4   7179369 PreferNotToAnswer   USA                  2022-06-19              
 5   7411970 less10k             USA                  2023-08-28              
 6   6260126 50k75k              USA                  2024-03-25              
 7   7570965 PreferNotToAnswer   USA                  2023-09-02              
 8   7412117 50k75k              USA                  2024-01-11              
 9   6252144 75k100k             USA                  2023-02-08              
10   8650154 50k75k              USA                  2023-05-28              
# ℹ more rows
# ℹ 1 more variable: TheBasics_Birthplace_date <date>

aou_survey(
  cohort,
  questions = c(1585811, 1585386),
  question_output = c("pregnancy", "insurance")
)

# Source:   SQL [?? x 5]
# Database: BigQueryConnection
   person_id pregnancy         insurance         pregnancy_date insurance_date
     <int64> <chr>             <chr>             <date>         <date>        
 1   6571348 <NA>              Yes               NA             2023-03-04    
 2   7875727 No                Yes               2023-02-08     2023-02-08    
 3   5072391 <NA>              Yes               NA             2023-03-27    
 4   6946539 <NA>              Yes               NA             2023-02-18    
 5   9204075 <NA>              PreferNotToAnswer NA             2021-11-07    
 6   9897520 <NA>              Yes               NA             2023-05-19    
 7   8521073 No                Yes               2023-04-24     2023-04-24    
 8   7183118 PreferNotToAnswer PreferNotToAnswer 2022-02-23     2022-02-23    
 9   8194272 <NA>              Yes               NA             2023-03-05    
10   9296127 <NA>              Yes               NA             2024-07-03    
# ℹ more rows

aou_survey(
  cohort,
  questions = c(1585375, 1586135, 1740719, 43529932),
  question_output = c("income", "birthplace", "grandpa_bowel_obstruction", "t2dm"),
  collect = FALSE
)

# Source:   SQL [?? x 9]
# Database: BigQueryConnection
   person_id grandpa_bowel_obstruction t2dm  income            birthplace
     <int64> <chr>                     <chr> <chr>             <chr>     
 1   6571348 <NA>                      <NA>  35k50k            USA       
 2   7875727 DontKnow                  No    PreferNotToAnswer Other     
 3   5072391 <NA>                      <NA>  PreferNotToAnswer USA       
 4   6946539 <NA>                      <NA>  10k25k            USA       
 5   9204075 <NA>                      <NA>  PreferNotToAnswer USA       
 6   9897520 <NA>                      <NA>  PreferNotToAnswer USA       
 7   8521073 No                        No    Skip              USA       
 8   7183118 PreferNotToAnswer         Skip  PreferNotToAnswer USA       
 9   8194272 <NA>                      <NA>  10k25k            USA       
10   9296127 <NA>                      <NA>  less10k           USA       
# ℹ more rows
# ℹ 4 more variables: grandpa_bowel_obstruction_date <date>, t2dm_date <date>,
#   income_date <date>, birthplace_date <date>

aou_survey(cohort,
  questions = 1384452,
  question_output = "osteoarthritis"
) %>%
  dplyr::count(osteoarthritis)

# Source:   SQL [?? x 2]
# Database: BigQueryConnection
  osteoarthritis          n
  <chr>             <int64>
1 <NA>               112366
2 DontKnow             9110
3 No                 103841
4 Skip                 6026
5 Yes                  9463
6 PreferNotToAnswer     353