---
title: "Queries Dataset Documentation"
output:
  rmarkdown::html_vignette:
  toc: false
vignette: >
  %\VignetteIndexEntry{Queries Dataset Documentation}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

library(admiraldev)
```

# Introduction

To support the safety analysis, it is quite common to define specific grouping
of events. One of the most common ways is to group events or medications by a
specific medical concept such as a Standard MedDRA Queries (SMQs) or WHO-Drug
Standardized Drug Groupings (SDGs).


To help with the derivation of these variables, the {admiral} function `derive_vars_query()` can be used.
This function takes as input the dataset (`dataset`) where the grouping must occur (e.g `ADAE`) and
a dataset containing the required information to perform the derivation of the grouping variables 
(`dataset_queries`).

The dataset passed to the `dataset_queries` argument of the
`derive_vars_query()` function can be created by the `create_query_data()`
function. For SMQs and SDGs company-specific functions for accessing the SMQ and
SDG database need to be passed to the `create_query_data()` function 
(see the description of the `get_terms_fun` argument for details).

This vignette describes the expected structure and content of the dataset passed to the
`dataset_queries` argument in the `derive_vars_query()` function. 

# Structure of the Query Dataset

## Variables

Variable | Scope |  Type | Example Value
------- | ----- | ------ | ----- 
**PREFIX** | The prefix used to define the grouping variables |  Character |  `"SMQ01"`
**GRPNAME** | The value provided to the grouping variables name|   Character | `"Immune-Mediated Guillain-Barre Syndrome"`
**SRCVAR** | The variable used to define the grouping. Used in conjunction with TERMCHAR |  Character | `"AEDECOD"`
**TERMCHAR** | A term used to define the grouping. Used in conjunction with SRCVAR |  Character | `"GUILLAIN-BARRE SYNDROME"`
**TERMNUM** | A code used to define the grouping. Used in conjunction with SRCVAR |  Integer | `10018767`
GRPID | Id number of the query. This could be a SMQ identifier |  Integer | `20000131`
SCOPE | Scope (Broad/Narrow) of the query |  Character | `BROAD`, `NARROW`, `NA`
SCOPEN | Scope (Broad/Narrow) of the query |  Integer | `1`, `2`, `NA`
VERSION | The version of the dictionary | Character | `"20.1"`

**Bold  variables** are required in `dataset_queries`: an error is issued if any of these variables is missing. `TERMCHAR` is only REQUIRED if there is character variable named in `SRCVAR`. `TERMNUM` is only REQUIRED if there is numeric variable named in `SRCVAR`. When `SRCVAR` contains both character and numeric variables, then both `TERMCHAR` and `TERMNUM` are required. Other variables are optional. 

The `VERSION` variable is not used by `derive_vars_query()` but can be used to
check if the dictionary version of the queries dataset and the analysis dataset
are in line.

## Required Content

Each row must be unique within the dataset.

As described above, the variables `PREFIX`, `GRPNAME`, `SRCVAR`, `TERMCHAR` and `TERMNUM` are required.
The combination of these variables will allow the creation of the grouping variable.

### Input

  + `PREFIX` must be a character string starting with 2 or 3 letters, followed by a 2-digits number (e.g. "CQ01").

  + `GRPNAME` must be a non missing character string and it must be unique within `PREFIX`. 

  + `SRCVAR` must be a non missing character string. 
  
    + Each value in `SRCVAR` represents a variable from `dataset` used to define the grouping variables (e.g. `AEDECOD`,`AEBODSYS`, `AELLTCD`).
    + The function `derive_vars_query()` will check that each value given in `SRCVAR` has a corresponding variable in the input  `dataset` and issue an error otherwise.
    
    + Different `SRCVAR` variables may be specified within a `PREFIX`.

  + `TERMCHAR` must be a character string. 
  This **must** be populated if `TERMNUM` is missing.
  
  + `TERMNUM` must be an integer. 
  This **must** be populated if `TERMCHAR` is missing.
  


### Output

  + `PREFIX` will be used to create the grouping variable appending the suffix "NAM". This variable will now be referred to as `ABCzzNAM`: the name of the grouping variable.

    + E.g. `PREFIX == "SMQ01"` will create the `SMQ01NAM` variable.


    + For each `PREFIX`, a new `ABCzzNAM` variable is created in `dataset`.


  + [`GRPNAME`]{#GRPNAME} will be used to populate the corresponding `ABCzzNAM` variable.

  + `SRCVAR` will be used to identify the variables from `dataset` used to perform the grouping (e.g. `AEDECOD`,`AEBODSYS`, `AELLTCD`).

  + `TERMCHAR` (for character variables), `TERMNUM` (for numeric variables) will be used to identify the records meeting the criteria in `dataset` based on the variable defined in `SRCVAR`.
  
  
  + **Result:**
  
    + For each record in `dataset`, where the variable defined by `SRCVAR` match a term from the `TERMCHAR` (for character variables) or `TERMNUM` (for numeric variables) in the `datasets_queries`, `ABCzzNAM` is populated with `GRPNAME`.
    
    
    + Note: The type (numeric or character) of the variable defined in `SRCVAR` is checked in `dataset`. If the variable is a character variable (e.g. `AEDECOD`), it is expected that `TERMCHAR` is populated, if it is a numeric variable (e.g. `AEBDSYCD`), it is expected that `TERMNUM` is populated, otherwise an error is issued.
    

### Example

In this example, one standard MedDRA query (`PREFIX = "SMQ01"`) and one customized query (`PREFIX = "CQ02"`) are  defined to analyze the adverse events. 

  + The standard MedDRA query variable `SMQ01NAM` [`PREFIX`] will be populated with "Standard Query 1" [`GRPNAME`] if any preferred term (`AEDECOD`) [`SRCVAR`] in `dataset` is equal to "AE1" or "AE2" [`TERMCHAR`]
  
  + The customized query (`CQ02NAM`) [`PREFIX`] will be populated with "Query 2" [`GRPNAME`] if any Low Level Term Code (`AELLTCD`)  [`SRCVAR`] in `dataset` is equal to 10  [`TERMNUM`] or any preferred term (`AEDECOD`) [`SRCVAR`] in `dataset` is equal to "AE4" [`TERMCHAR`].
  
#### Query Dataset (`ds_query`)
  
PREFIX | GRPNAME | SRCVAR | TERMCHAR | TERMNUM |
------- | ----- | ------ | ----- | ----- | -----
SMQ01| Standard Query 1 | AEDECOD | AE1 | 
SMQ01| Standard Query 1 | AEDECOD | AE2 | 
CQ02| Query 2 | AELLTCD | | 10
CQ02| Query 2 | AEDECOD | AE4| 

#### Adverse Event Dataset (`ae`)
  
USUBJID | AEDECOD | AELLTCD 
------- | ----- | ------ 
0001 | AE1 | 101 | 
0001 | AE3 | 10| | 
0001 | AE4 | 120 | 
0001 | AE5 | 130 |


#### Output Dataset

Generated by calling  `derive_vars_query(dataset = ae, dataset_queries = ds_query)`.

USUBJID | AEDECOD | AELLTCD | SMQ01NAM |CQ02NAM
------- | ----- | ------ | ----- | ----- 
0001 | AE1 | 101 | Standard Query 1 |
0001 | AE3 | 10| | Query 2
0001 | AE4 | 120 |  | Query 2
0001 | AE5 | 130 |  |


Subject 0001 has one event meeting the Standard Query 1 criteria (`AEDECOD = "AE1"`) and two events meeting the customized query (`AELLTCD = 10` and `AEDECOD = "AE4"`). 


## Optional Content


When standardized MedDRA Queries are added to the dataset, it is expected that the name of the query (`ABCzzNAM`) is populated along with its number code (`ABCzzCD`),  and its Broad or Narrow scope (`ABCzzSC`).

The following variables can be added to `queries_datset` to derive this information.


### Input

  + `GRPID` must be an integer. 
  
  + `SCOPE` must be a character string. Possible values are: "BROAD", "NARROW" or `NA`. 
  
  + `SCOPEN` must be an integer. Possible values are: `1`, `2` or `NA`. 

### Output

  + `GRPID`, `SCOPE` and `SCOPEN` will be used in the same way as `GRPNAME` [(see here)](#GRPNAME) and will help in the creation of the `ABCzzCD`, `ABCzzSC` and `ABCzzSCN` variables.
  
### Output Variables

These variables are optional and if not populated in `dataset_queries`, the corresponding output variable will not be created:


PREFIX | GRPNAME | GRPID | SCOPE |SCOPEN | **Variables created**
------- | ----- | ------ | ----- | ----- | -----
SMQ01| Query 1 | XXXXXXXX | NARROW | 2 | `SMQ01NAM`, `SMQ01CD`, `SMQ01SC`, `SMQ01SCN` 
SMQ02| Query 2 | XXXXXXXX |BROAD  | | `SMQ02NAM`, `SMQ02CD`, `SMQ02SC`
SMQ03| Query 3 | XXXXXXXX |  |1 | `SMQ03NAM`, `SMQ03CD`, `SMQ03SCN`
SMQ04| Query 4 | XXXXXXXX |  | | `SMQ04NAM`, `SMQ04CD`
SMQ05| Query 5|  |  | | `SMQ05NAM`


