variable_details worksheet

Source: vignettes/variable_details.Rmd

Introduction

The worksheet variable_details maps variables across datasets. variable_details also contains the instructions for function rec_with_table on how to recode the variables listed in the worksheet variables.

Specifically, the function rec_with_table recodes variables listed in column variable_details$variableStart to the variables listed in variable_details$variable.

#> In the dataframe `variable_details` there are 114 rows and 14 columns

Structure of the worksheet variable_details

Naming convention for not applicable and missing values

rec_with_table() uses the tagged_na() function from the haven package to tag not applicable responses as NA(a), and missing values (don’t know, refusal, not stated) as NA(b). As you will see later, not applicable values are recoded to NA::a, and missing values are recoded to NA::b. See tagged_na_usage for more information about tagged_na().

Rows

Each row in variable_details gives instructions to recode a single category of a final variable. Typically, a single category of a final variable maps to a single category of an original variable. Therefore, in variable_details you’ll have one row per category.

There are two exceptions to this rule.

  1. Original categories are sometimes combined into a single final variable category. For example,
  • Most often survey responses “don’t know,” “refusal,” and “not stated” are combined into a final single “missing” category.
  • Original age group categories are often combined into larger age groups categories (e.g., original five-year age group categories are combined into final ten-year age group categories).
  1. Original datasets with different categories map to separate rows for a final variable. For example,
  • The final variable, “Sex,” has categories “1” = male and “2” = female.
  • Dataset A has sex categories “m” = male and “f” = female.
  • Dataset B has categories “1” = male and “2” = female.
  • Variable_details requires four rows to map the final “Sex” categories = two for dataset A and two for dataset B.

Note: We recommend not combining variables if the variable has an important change between datasets. variable_detailsdetails$notes is used to identify issues that may be relevant when recoding the variable or category.

Columns

Here we explain the columns in the worksheet variable_details.

Table 1. Summary of the source and level of information used for each column in the worksheet ‘variable_details’.

Level of information
Data_source Dataset Variable Categories
recoded variable * recEnd *
recoded typeEnd * numValidCat
recoded catLabel
recoded catLongLabel
recoded units
original databaseStart variableStart * recStart *
original typeStart * catStartLabel
original variableStartlabel
both notes notes notes
* Columns required for the function rec_with_table. All other columns are recommended but optional.

The order of the columns within the worksheet variable_details is not important because recodeflow functions use the column names.

  1. variable: the name of the final recoded variable.
variable
trt
trt
trt
trt
trt
age
age
  1. typeEnd: the data type of the final recoded variable. If the recoded variable is continuous fill in as cont; if the recoded variable that is categorical fill in as cat .
variable typeEnd
7 trt cat
8 trt cat
9 trt cat
10 trt cat
11 trt cat
12 age cont
13 age cont
  1. typeStart: the variable type as indicated in datasets. As indicated in the typeEnd column, categorical variables are denoted as cat and continuous variables are denoted as cont.
variable typeEnd typeStart
7 trt cat cat
8 trt cat cat
9 trt cat cat
10 trt cat cat
11 trt cat cat
12 age cont cont
13 age cont cont
  1. databaseStart: the datasets that contain the variable and categories of interest, separated by commas.
variable typeEnd typeStart databaseStart
7 trt cat cat tester1, tester2
8 trt cat cat tester1, tester2
9 trt cat cat tester1, tester2
10 trt cat cat tester1, tester2
11 trt cat cat tester1, tester2
12 age cont cont tester1, tester2
13 age cont cont tester1, tester2
  1. variableStart: the original names of the variables as they are listed in the original datasets, separated by commas.
  • If the variable name in a particular dataset is different from the recoded variable name, write out the dataset name, add two colons, and write out the original variable name for that dataset.
  • If the variable name in a particular dataset is the same as the recoded variable name, write the variable name in square brackets. Note: this only needs to be written out once.
variable typeEnd typeStart databaseStart variableStart
7 trt cat cat tester1, tester2 [trt]
8 trt cat cat tester1, tester2 [trt]
9 trt cat cat tester1, tester2 [trt]
10 trt cat cat tester1, tester2 [trt]
11 trt cat cat tester1, tester2 [trt]
12 age cont cont tester1, tester2 [age]
13 age cont cont tester1, tester2 [age]
  1. variableStartLabel: short form label describing the original variable.
variable typeEnd typeStart databaseStart variableStart variableStartLabel
7 trt cat cat tester1, tester2 [trt] treatment
8 trt cat cat tester1, tester2 [trt] treatment
9 trt cat cat tester1, tester2 [trt] treatment
10 trt cat cat tester1, tester2 [trt] treatment
11 trt cat cat tester1, tester2 [trt] treatment
12 age cont cont tester1, tester2 [age] age
13 age cont cont tester1, tester2 [age] age
  1. numValidCat: the number of categories for the recoded variable. This only applies to variables in which the typeEnd is cat. For continuous variables, numValidCat = N/A. Note that for categories: not applicable, missing, and else, are not included in the category count. This column is not necessary for the function rec_with_table().
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat
7 trt cat cat tester1, tester2 [trt] treatment 3
8 trt cat cat tester1, tester2 [trt] treatment 3
9 trt cat cat tester1, tester2 [trt] treatment 3
10 trt cat cat tester1, tester2 [trt] treatment 3
11 trt cat cat tester1, tester2 [trt] treatment 3
12 age cont cont tester1, tester2 [age] age N/A
13 age cont cont tester1, tester2 [age] age N/A
  1. recEnd: the category of the categorical value your recoding to. For the not applicable category, write NA::a and for missing and else categories, write NA::b. For continuous variables that are not recoded in type, you would write in this column copy so that the function copies the values without any recoding.
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd
7 trt cat cat tester1, tester2 [trt] treatment 3 1
8 trt cat cat tester1, tester2 [trt] treatment 3 2
9 trt cat cat tester1, tester2 [trt] treatment 3 3
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b
12 age cont cont tester1, tester2 [age] age N/A copy
13 age cont cont tester1, tester2 [age] age N/A NA::b
  • For categorical variables that remain categorical (i.e. from: cat -> to: cat), it is ideal to retain the same values as indicated in the original datasets.
  • For categorical variables that have changed in type (i.e from: cat -> to: cont), you will have to develop values that make the most sense to your analysis. For example, for variables that change from: cat -> to: cont, you can choose the categories’ lower bound, mid-point, or upper-bound for the continuous value.
  1. catLabel: short form label describing the category of a particular variable.
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel
7 trt cat cat tester1, tester2 [trt] treatment 3 1 D-penicillmain
8 trt cat cat tester1, tester2 [trt] treatment 3 2 Placebo
9 trt cat cat tester1, tester2 [trt] treatment 3 3 none
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a N/A
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b missing
12 age cont cont tester1, tester2 [age] age N/A copy N/A
13 age cont cont tester1, tester2 [age] age N/A NA::b N/A
  1. catLabelLong: more detailed label describing the category of a particular variable. This label should be identical to what is recorded in the original data’s documentation (e.g., the exact survey question). For derived variables, you can create your own long label.
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel catLabelLong
7 trt cat cat tester1, tester2 [trt] treatment 3 1 D-penicillmain D-penicillmain
8 trt cat cat tester1, tester2 [trt] treatment 3 2 Placebo Placebo
9 trt cat cat tester1, tester2 [trt] treatment 3 3 none no treatment
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a N/A N/A
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b missing missing
12 age cont cont tester1, tester2 [age] age N/A copy N/A N/A
13 age cont cont tester1, tester2 [age] age N/A NA::b N/A N/A
  1. units: the units of a particular variable. If there are no units for the variable, write N/A. Note, the function will not work if there different units between the rows of the same variable (e.g. if the concentration of a single molecule has two records one recorded as mg/dl and the other recorded as g/dl).
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel catLabelLong units
7 trt cat cat tester1, tester2 [trt] treatment 3 1 D-penicillmain D-penicillmain N/A
8 trt cat cat tester1, tester2 [trt] treatment 3 2 Placebo Placebo N/A
9 trt cat cat tester1, tester2 [trt] treatment 3 3 none no treatment N/A
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a N/A N/A N/A
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b missing missing N/A
12 age cont cont tester1, tester2 [age] age N/A copy N/A N/A years
13 age cont cont tester1, tester2 [age] age N/A NA::b N/A N/A years
  1. recStart: the range of values for a particular category in a variable as indicated in the original data. See data documentation and use the smallest and large values as your range to capture all values across the datasets.

The rules for each category of a new variable are a string in recStart and value in recEnd. These recode pairs are the same syntax as interval notation in which a closed range of values are specified using square brackets. See Notations for intervals for more information on interval notation. Recode pairs are obtained from the recStart and recEnd columns value range is indicated by a comma, e.g. recStart= [1,4]; recEnd = 1 (recodes all values from 1 to 4 into 1} value range for double vectors (with fractional part), all values within the specified range are recoded; e.g. recStart = [1,2.5]; recEnd = 1 recodes 1 to 2.5 into 1, but 2.55 would not be recoded (since it’s not included in the specified range). NA is used for missing values (don’t know, refusal, not stated) else is used all other values, which have not been specified yet, are indicated by else, e.g. recStart = "else"; recEnd = NA (recode all other values (not specified in other rows) to “NA”)} copy the else token can be combined with copy, indicating that all remaining, not yet recoded values should stay the same (are copied from the original value), e.g. recStart = "else"; recEnd = "copy"

variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel
7 trt cat cat tester1, tester2 [trt] treatment 3 1 D-penicillmain
8 trt cat cat tester1, tester2 [trt] treatment 3 2 Placebo
9 trt cat cat tester1, tester2 [trt] treatment 3 3 none
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a N/A
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b missing
12 age cont cont tester1, tester2 [age] age N/A copy N/A
13 age cont cont tester1, tester2 [age] age N/A NA::b N/A
  1. catStartLabel: label describing each category. This label should be identical to what is shown in the original data documentation. For the missing rows, each missing category is described along with their coded values. You can import labels from a survey’s DDI files using bllflow helper functions. See bllflow documentation.
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel catLabelLong units recStart catStartLabel
7 trt cat cat tester1, tester2 [trt] treatment 3 1 D-penicillmain D-penicillmain N/A 1 D-penicillmain
8 trt cat cat tester1, tester2 [trt] treatment 3 2 Placebo Placebo N/A 2 Placebo
9 trt cat cat tester1, tester2 [trt] treatment 3 3 none no treatment N/A 3 none
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a N/A N/A N/A NA N/A
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b missing missing N/A else else
12 age cont cont tester1, tester2 [age] age N/A copy N/A N/A years [25,79] age
13 age cont cont tester1, tester2 [age] age N/A NA::b N/A N/A years else else
  1. notes: any relevant notes to inform the user running the recode-with-table function. Things to include here would be changes in wording across datasets, missing/changes in categories, and changes in variable type across datasets.
variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel catLabelLong units recStart catStartLabel notes
7 trt cat cat tester1, tester2 [trt] treatment 3 1 D-penicillmain D-penicillmain N/A 1 D-penicillmain This is sample survival pbc data
8 trt cat cat tester1, tester2 [trt] treatment 3 2 Placebo Placebo N/A 2 Placebo This is sample survival pbc data
9 trt cat cat tester1, tester2 [trt] treatment 3 3 none no treatment N/A 3 none This is sample survival pbc data
10 trt cat cat tester1, tester2 [trt] treatment 3 NA::a N/A N/A N/A NA N/A This is sample survival pbc data
11 trt cat cat tester1, tester2 [trt] treatment 3 NA::b missing missing N/A else else This is sample survival pbc data
12 age cont cont tester1, tester2 [age] age N/A copy N/A N/A years [25,79] age This is sample survival pbc data
13 age cont cont tester1, tester2 [age] age N/A NA::b N/A N/A years else else This is sample survival pbc data

Derived Variables

The same naming convention applies to derived variables with the exception of two columns:

  1. In variableStart, instead of database names being listed, DerivedVar:: is written followed with the list of original variables used inside square brackets.
  • DerivedVar::[var1, var2, var3]
  1. In recEnd, write Func:: followed with the exact name of the custom function used to create the derived variable.
  • Func::derivedFunction

A derived variable looks like this in variable_details.csv

variable typeEnd typeStart databaseStart variableStart variableStartLabel numValidCat recEnd catLabel catLabelLong units recStart catStartLabel notes
64 example_der cont cont tester1, tester2 DerivedVar::[chol, bili] example_der N/A Func::example_der_fun N/A N/A mg/dl else N/A This is sample survival pbc data