Skip to contents

Creates a queries dataset as input dataset to the dataset_queries argument in the derive_vars_query() function as defined in the Queries Dataset Documentation.

Usage

create_query_data(
  queries,
  meddra_version = NULL,
  whodd_version = NULL,
  get_smq_fun = NULL,
  get_sdg_fun = NULL
)

Arguments

queries

List of queries

A list of query() objects is expected.

meddra_version

MedDRA version

The MedDRA version used for coding the terms in the AE dataset should be specified. If any of the queries is a SMQ or a customized query including a SMQ, the parameter needs to be specified.

Permitted Values: A character string (the expected format is company-specific)

whodd_version

WHO Drug Dictionary version

The version of the WHO Drug Dictionary used for coding the terms in the CM dataset should be specified. If any of the queries is a SDG, the parameter needs to be specified.

Permitted Values: A character string (the expected format is company-specific)

get_smq_fun

Function which returns the terms of an SMQ

For each query specified for the queries parameter which refers to an SMQ (i.e., those where the definition field is set to a smq_select() object or a list which contains at least one smq_select() object) the specified function is called to retrieve the terms defining the query. This function is not provided by admiral as it is company specific, i.e., it has to be implemented at company level.

The function must return a dataset with all the terms defining the SMQ. The output dataset must contain the following variables.

  • TERM_LEVEL: the variable to be used for defining a term of the SMQ, e.g., AEDECOD

  • TERM_NAME: the name of the term if the variable TERM_LEVEL is referring to is character

  • TERM_ID the numeric id of the term if the variable TERM_LEVEL is referring to is numeric

  • QUERY_NAME: the name of the SMQ. The values must be the same for all observations.

The function must provide the following parameters

  • smq_select: A smq_select() object.

  • version: The MedDRA version. The value specified for the meddra_version in the create_query_data() call is passed to this parameter.

  • keep_id: If set to TRUE, the output dataset must contain the QUERY_ID variable. The variable must be set to the numeric id of the SMQ.

  • temp_env: A temporary environment is passed to this parameter. It can be used to store data which is used for all SMQs in the create_query_data() call. For example if the SMQs need to be read from a database all SMQs can be read and stored in the environment when the first SMQ is handled. For the other SMQs the terms can be retrieved from the environment instead of accessing the database again.

get_sdg_fun

Function which returns the terms of an SDG

For each query specified for the queries parameter which refers to an SDG the specified function is called to retrieve the terms defining the query. This function is not provided by admiral as it is company specific, i.e., it has to be implemented at company level.

The function must return a dataset with all the terms defining the SDG. The output dataset must contain the following variables.

  • TERM_LEVEL: the variable to be used for defining a term of the SDG, e.g., CMDECOD

  • TERM_NAME: the name of the term if the variable TERM_LEVEL is referring to is character

  • TERM_ID the numeric id of the term if the variable TERM_LEVEL is referring to is numeric

  • QUERY_NAME: the name of the SDG. The values must be the same for all observations.

The function must provide the following parameters

  • sdg_select: A sdg_select() object.

  • version: The WHO drug dictionary version. The value specified for the whodd_version in the create_query_data() call is passed to this parameter.

  • keep_id: If set to TRUE, the output dataset must contain the QUERY_ID variable. The variable must be set to the numeric id of the SDG.

  • temp_env: A temporary environment is passed to this parameter. It can be used to store data which is used for all SDGs in the create_query_data() call. For example if the SDGs need to be read from a database all SDGs can be read and stored in the environment when the first SDG is handled. For the other SDGs the terms can be retrieved from the environment instead of accessing the database again.

Value

A dataset to be used as input dataset to the dataset_queriesargument in derive_vars_query()

Details

For each query() object listed in the queries argument, the terms belonging to the query (TERM_LEVEL, TERM_NAME, TERM_ID) are determined with respect to the definition field of the query: if the definition field of the query() object is

  • an smq_select() object, the terms are read from the SMQ database by calling the function specified for the get_smq_fun parameter.

  • an sdg_select() object, the terms are read from the SDG database by calling the function specified for the get_sdg_fun parameter.

  • a data frame, the terms stored in the data frame are used.

  • a list of data frames and smq_select() objects, all terms from the data frames and all terms read from the SMQ database referenced by the smq_select() objects are collated.

The following variables (as described in Queries Dataset Documentation) are created:

  • VAR_PREFIX: Prefix of the variables to be created by derive_vars_query() as specified by the prefix element.

  • QUERY_NAME: Name of the query as specified by the name element.

  • QUERY_ID: Id of the query as specified by the id element. If the id element is not specified for a query, the variable is set to NA. If the id element is not specified for any query, the variable is not created.

  • QUERY_SCOPE: scope of the query as specified by the scope element of the smq_select() object. For queries not defined by a smq_select() object, the variable is set to NA. If none of the queries is defined by a smq_select() object, the variable is not created.

  • QUERY_SCOPE_NUM: numeric scope of the query. It is set to 1 if the scope is broad. Otherwise it is set to '2'. If the add_scope_num element equals FALSE, the variable is set to NA. If the add_scope_num element equals FALSE for all SMQs or none of the queries is an SMQ , the variable is not created.

  • TERM_LEVEL: Name of the variable used to identify the terms.

  • TERM_NAME: Value of the term variable if it is a character variable.

  • TERM_ID: Value of the term variable if it is a numeric variable.

Author

Stefan Bundfuss

Examples

library(tibble)
library(magrittr, warn.conflicts = FALSE)
library(dplyr, warn.conflicts = FALSE)
library(admiral.test)
library(admiral)

# creating a query dataset for a customized query
cqterms <- tribble(
  ~TERM_NAME, ~TERM_ID,
  "APPLICATION SITE ERYTHEMA", 10003041L,
  "APPLICATION SITE PRURITUS", 10003053L
) %>%
  mutate(TERM_LEVEL = "AEDECOD")

cq <- query(
  prefix = "CQ01",
  name = "Application Site Issues",
  definition = cqterms
)

create_query_data(queries = list(cq))
#> # A tibble: 2 x 5
#>   TERM_NAME                 TERM_ID TERM_LEVEL VAR_PREFIX QUERY_NAME            
#>   <chr>                       <int> <chr>      <chr>      <chr>                 
#> 1 APPLICATION SITE ERYTHE… 10003041 AEDECOD    CQ01       Application Site Issu…
#> 2 APPLICATION SITE PRURIT… 10003053 AEDECOD    CQ01       Application Site Issu…

# create a query dataset for SMQs
pregsmq <- query(
  prefix = "SMQ02",
  id = auto,
  definition = smq_select(
    name = "Pregnancy and neonatal topics (SMQ)",
    scope = "NARROW"
  )
)

bilismq <- query(
  prefix = "SMQ04",
  definition = smq_select(
    id = 20000121L,
    scope = "BROAD"
  )
)

# The get_smq_terms function from admiral.test is used for this example.
# In a real application a company-specific function must be used.
create_query_data(
  queries = list(pregsmq, bilismq),
  get_smq_fun = admiral.test:::get_smq_terms,
  meddra_version = "20.1"
)
#> # A tibble: 43 x 6
#>    TERM_NAME         TERM_LEVEL QUERY_NAME       QUERY_ID QUERY_SCOPE VAR_PREFIX
#>    <chr>             <chr>      <chr>               <int> <chr>       <chr>     
#>  1 Achromotrichia c… AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  2 Craniosynostosis  AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  3 Hypophosphatasia  AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  4 Congenital pyelo… AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  5 Uterine contract… AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  6 Ductus arteriosu… AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  7 Pseudotruncus ar… AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  8 Lipomeningocele   AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#>  9 Macrocephaly      AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#> 10 Carnitine palmit… AEDECOD    Pregnancy and n… 20000185 NARROW      SMQ02     
#> # … with 33 more rows

# create a query dataset for SDGs
sdg <- query(
  prefix = "SDG01",
  id = auto,
  definition = sdg_select(
    name = "5-aminosalicylates for ulcerative colitis"
  )
)

# The get_sdg_terms function from admiral.test is used for this example.
# In a real application a company-specific function must be used.
create_query_data(
  queries = list(sdg),
  get_sdg_fun = admiral.test:::get_sdg_terms,
  whodd_version = "2019-09"
)
#> # A tibble: 16 x 5
#>    TERM_NAME               TERM_LEVEL QUERY_NAME             QUERY_ID VAR_PREFIX
#>    <chr>                   <chr>      <chr>                     <int> <chr>     
#>  1 AMINOSALICYLIC ACID     CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  2 AMINOSALICYLATE CALCIUM CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  3 AMINOSALICYLATE CALCIU… CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  4 AMINOSALICYLATE SODIUM  CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  5 SODIUM AMINOSALICYLATE… CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  6 AMINOSALICYLATE SODIUM… CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  7 SULFASALAZINE           CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  8 CALCIUM BENZAMIDOSALIC… CMDECOD    5-aminosalicylates fo…      220 SDG01     
#>  9 OLSALAZINE              CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 10 OLSALAZINE SODIUM       CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 11 MESALAZINE              CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 12 BALSALAZIDE             CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 13 BALSALAZIDE SODIUM      CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 14 BALSALAZIDE DISODIUM D… CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 15 DERSALAZINE             CMDECOD    5-aminosalicylates fo…      220 SDG01     
#> 16 DERSALAZINE SODIUM      CMDECOD    5-aminosalicylates fo…      220 SDG01     

# creating a query dataset for a customized query including SMQs
# The get_smq_terms function from admiral.test is used for this example.
# In a real application a company-specific function must be used.
create_query_data(
  queries = list(
    query(
      prefix = "CQ03",
      name = "Special issues of interest",
      definition = list(
        smq_select(
          name = "Pregnancy and neonatal topics (SMQ)",
          scope = "NARROW"
        ),
        cqterms
      )
    )
  ),
  get_smq_fun = admiral.test:::get_smq_terms,
  meddra_version = "20.1"
)
#> # A tibble: 23 x 5
#>    TERM_NAME                   TERM_LEVEL QUERY_NAME          TERM_ID VAR_PREFIX
#>    <chr>                       <chr>      <chr>                 <int> <chr>     
#>  1 Achromotrichia congenital   AEDECOD    Special issues of …      NA CQ03      
#>  2 Craniosynostosis            AEDECOD    Special issues of …      NA CQ03      
#>  3 Hypophosphatasia            AEDECOD    Special issues of …      NA CQ03      
#>  4 Congenital pyelocaliectasis AEDECOD    Special issues of …      NA CQ03      
#>  5 Uterine contractions durin… AEDECOD    Special issues of …      NA CQ03      
#>  6 Ductus arteriosus prematur… AEDECOD    Special issues of …      NA CQ03      
#>  7 Pseudotruncus arteriosus    AEDECOD    Special issues of …      NA CQ03      
#>  8 Lipomeningocele             AEDECOD    Special issues of …      NA CQ03      
#>  9 Macrocephaly                AEDECOD    Special issues of …      NA CQ03      
#> 10 Carnitine palmitoyltransfe… AEDECOD    Special issues of …      NA CQ03      
#> # … with 13 more rows