3.2.1 Add individual files to SQLite database using add_to_database.

The function add_to_database can be used to add individual files to the SQLite database. Start by defining and connecting to your SQLite database. In this article we create a temporary database, but in practice this would be a permanent storage location. Specifically, file.path(tempdir(), “temp.sqlite”) would be replaced by the desired file path and SQLite database name.

Next, we add medical diagnoses data from the observation files to this database using the add_to_database function. It is imperative that when adding raw CPRD data to an SQLite database, that the SQLite database itself is stored in a secure environment which aligns with the data storage requirements of CPRD. The simulated raw data provided with rcprd can be accessed using the system.file function. The vector of patient id’s that defines the cohort is defined through the subset_patids argument. Only data with patid’s matching this argument will be added to the SQLite database. The filetype argument will select an appropriate function for reading in the.txt files, and also defines the name of the table in the SQLite database that the files are added to. Note that for the first file, overwrite = TRUE is specified to create a new table. For the second and third file, append = TRUE is specified to append to an existing table.

We can then query this database using the db_query function and return the first 3 rows. We will showcase how to use this function to query the database for specific codes in section 3.3.3. db_query utilises the package RSQLite, and more details on how to query an SQLite database from within R is available in RSQLite’s documentation [12].

Next, the prescription data from the drugissue files is added to a table called drugissue. A single SQLite database may contain more than one table, so this data is added to a different table within the same SQLite database. The table will take the same name as the filetype argument, unless the table_name argument is specified.

This table can be queried in the same way, changing the tab argument, which specifies the name of the table in the SQLite database to query:

Listing the tables in the SQLite database shows there are now two, named observation and drugissue.

The add_to_database function allows specification of filetype = c(“observation”, “drugissue”, “referral”, “problem”, “consultation”, “hes_primary”,”death”), each corresponding to a specific function for reading in the corresponding.txt files with correct formatting. The “hes_primary” options correspond to the primary diagnoses file in linked HES APC data. The “death” file corresponds to the death file in the linked ONS data. If wanting to add other files to the SQLite database, a user defined function for reading in the raw.txt file can be specified through extract_txt_func, and a table name can be specified through tablename. This allows the user to add any.txt file to their SQLite database.

Finally, when manually adding files in this manner, it is good practice to close the connection to the SQLite database once finished.

3.2.2 Add all relevant files to SQLite database using cprd_extract.

In practice, there will be a high number of files to add to the SQLite database and adding each one using add_to_database would be cumbersome. We now repeat the extraction but using the cprd_extract function, which is a wrapper for add_to_database, and will add all the files in a specified directory that contain a string matching the specified file type. Start by creating a connection to a new database:

We then use cprd_extract to add all the observation files into the SQLite database. If the connection (aurum_extract) is to an existing database, which is the case here, it will be overwritten when running cprd_extract. The directory containing the files should be specified using filepath. It will only read in and add files with the text string specified in filetype in their file name The filetype argument takes values in c(“observation”, “drugissue”, “referral”, “problem”, “consultation”). We then query the first three rows of this database, and note they are the same as previously.

The process is then repeated for the drugissue files.

The string to match on, function to read in the raw data, and the name of the table in the SQLite database, can be altered using the str_match, extract_txt_func and tablename arguments respectively. Note the use of str_match may be of particular importance if the naming convention of the raw data differs from what we have described above. The argument rm_duplicates = TRUE can be specified to de-duplicate records before adding into the SQLite database. This will increase computation time, and the derivation of many variables will not be effected by having duplicate records, so consider carefully whether it’s necessary to apply this step or not. There will also be the opportunity to de-duplicate the records later on when querying the data. Note that this function may run for a considerable period of time when working with the entire CPRD AURUM database, and therefore it is not recommended to run interactively. While creation of the SQLite database may be time consuming, subsequent queries will be far more efficient, so this is short term pain for a long term gain.

3.2.3 Add all relevant files to SQLite database in a computationally efficient manner using the set functionality.

When the number of patients in your cohort is very large (for example millions, or tens of millions), the add_to_database function may perform very slowly. This is because for each observation in the file being added to the SQLite database, add_to_database checks to see whether the patid is contained in the vector subset_patids (a vector of length 20,000,000 in our case). We can utilise the structure of the CPRD AURUM data to speed up this process. If data has the set naming convention (see section 2.1), we know that we only need to search for patids from subset_patids, that are in the corresponding patient file. For example, when reading in file aurum_allpatid_set1_extract_observation_00Y.txt (for any Y), we only need to search whether patid is in the vector of patids from subset.patid, that are also in aurum_allpatid_set1_extract_patient_001.txt, which is much smaller vector. This can reduce the computation time for add_to_database and cprd_extract.

To achieve this, the subset_patids object should be a data frame with two required columns. The first column should be patid, the second should be set, reporting the corresponding value of set which the patient belongs to. The first step is therefore to create a patient file, which has an extra variable set, the number following the text string set in the patient file containing data for that patient. When reading in the patient files to create a cohort, this can be done by specifying set = TRUE. In this example, all individuals in our cohort come from the file with string set1, and therefore this variable is the same for all individuals in this cohort, however this will not be the case in practice.

The patient file read in is the same as previously, with the addition of the set column. This file can be reduced to just the patid and set columns, and used as the input to subset_patids when running the add_to_database and cprd_extract functions. When extracting data from observation files with set1 in the name, it will only search for patient id’s with set == 1 in the data.frame provided to subset_patids.

The resulting SQLite database is the same as those from sections 3.2.1 and 3.2.2. The computational gains from applying the subsetting in this manner will not be realised in this example given the size of the dataset, however may be worthwhile in practice when working with large cohorts. We do not close the connection, as we will now move onto querying the database to extract variables for creating an analysis-ready dataset.

3.3.1 Functions for extracting common variable types.

There are functions to extract three common variable types, history of condition/medication prior to index date (extract_ho), time from the index date until first occurrence of a medical code/prescription or censoring (extract_time_until), and most recent test result(s) in a given time frame and valid range relative to the index date (extract_test_data).

The first, extract_ho, extracts a binary variable based on whether individual has a specified code recorded prior to index date. This can be applied to search for history of medical diagnoses or prescriptions. The index date must be a variable in the cohort dataset, and is specified through the indexdt argument.

The second is extract_time_until, which defines a time-to-event/survival variable. This has two components, the time until the first record of a specified code or censoring, and an indicator for whether event was observed or censored. To derive a variable of this type the cohort must also contain a time until censoring variable, which can be specified through censdt.

The third is extract_test, which will extract the most recent test result in a given time frame. The number of days before and after the index date to search for results are specified through time_post and time_prev respectively. Test results are identified from the observation file, using code lists. Lower and upper bounds can also be specified for the extracted data through lower_bound and upper_bound.

More than one observation can be returned by specifying numobs. Metadata of the test result, such as the unit of measurement, date recorded, and the medical code, can be returned by settings numunitid = TRUE. A variation of this function, extract_test_data_var, will returns the standard deviation of the test data within the specified time and value range. Once all the variables of interest have been extracted, they can be merged into an analysis-ready dataset (step 4).

The codelists can also be specified through an R data.frame which must contain either a medcodeid or prodcodeid column. This may allow the user to run sensitivity analyses more easily if they would like to extract the same variable for different subgroups of the same codelist. For example:

Codelists can be specified in this way for any of the functions in this section, or sections 3.3.2 and 3.3.3. However, the extra variables from the codelist (i.e., the condition and subgroup variables in the above example) will only be returned in the output when it is meaningful to do so. For example, in extract_ho, an individual may have many matching codes in their medical history, and therefore it’s unclear which should be returned.

3.3.2 Functions for extracting specific variables.

There are also a number of functions that can be used to extract specific variables:

• extract_bmi: Derives BMI scores. Requires specification of codelist for BMI, height, and weight separately.
• extract_cholhdl_ratio: Derives total cholesterol/high-density lipoprotein ratio. Requires specification of separate codelists for total cholesterol/high-density lipoprotein ratio, total cholesterol, and high-density lipoproteins separately.
• extract_diabetes: Derives a categorical variable for history of type 1 diabetes, history of type 2 diabetes or no history of diabetes. Requires specification of separate codelists for type 1 and type 2 diabetes. Individuals with codes for both are designated as type 1.
• extract_smoking: Derives a categorical variable for smoking status. Requires specification of seperate codelists for non-smoker, ex-smoker, light smoker, moderate smoker and heavy smoker. If the most recent smoking status is non-smoker, but there are historical codes which indicate smoking, then individual will be classified as an ex-smoker.

It was deemed that these variables required custom functions because their definitions did not fit into any of the variable types from section 3.3.1. In each case, a number of steps are taken in order to clean or manipulate the data in order to get the desired output. For example, height measurements recorded in centimeters are converted to metres in order to calculate BMI scores. This is done through the use of the numunitid variable in the observation file. For both BMI and cholesterol/high-density lipoprotein ratio, the variable can be either be identified directly, or calculated from the component mesaures. In each case, the component parts must be recorded in the specified time range relative to the index date. For smoking status, if an individuals most recent medical observation was recorded as a non-smoker, but their medical record shows previous smoking, the most recent record is changed to ex-smoker. For diabetes status, diabetes if often recorded with generic codes such as “diabetes mellitus”, which does not specify which type. This is dealt with by assuming all generic codes refer to type 2 diabetes, unless that individual also has a specific type 1 diabetes code, in which case they will be determined to have type 1 diabetes as opposed to type 2.

The full details on extracting these variables are provided in the vignette titled Details-on-algorithms-for-extracting-specific-variables. However, it is important to state, that the correct way to define a variable may change from study to study. Therefore when using these functions to extract variables, we encourage taking the time to ensure that the way the variable is extracted matches the definition in ones study, and edit these functions and algorithms accordingly.

3.3.4 Saving extracted variables directly to a disk drive, and utilising rAURUMs suggested directory system.

So far all extracted variables (using functions from section 3.3.1 and 3.3.2) have been read into the R workspace by specifying return_output = TRUE. When working with large cohorts it may be preferable to save the output directly onto a disk drive, by specifying out_save_disk = TRUE. The file path to save the output can be specified manually through the out_filepath argument. However, if this argument is left as NULL, rcprd will attempt to save the extracted variable into a directory “data/extraction/” relative to the working directory. The name of the file itself will be dependent on the variable name specified through argument varname. This can be a very convenient way to save the output directly to disk without having to repeatedly specify file paths and file names.

There is similar functionality when specifying the codelists. Codelists can be specified in two ways. The first is to read the codelist into R as a character vector and then specify through the argument codelist_vector, which has been done in all the previous examples. Alternatively, codelists stored on the disk drive can be referred to from the codelist argument in many rcprd functions, but requires a specific underlying directory structure. The codelist on the disk drive must be stored in a directory called “codelists/analysis/” relative to the working directory. The codelist must be a.csv file, and contain a column medcodeid, prodcodeid or ICD10 depending on the table being queried. The input to argument codelist should just be a character string of the name of the files (excluding the suffix ‘.csv’). The codelist_vector argument will take precedence over the codelist argument if both are specified.

Finally, there is similar functionality for accessing the SQLite database internally, rather than having to 1) open a connection, 2) use this as an input in the functions, and then 3) remember to close the connection. Instead, if the SQLite database is stored in a directory “data/sql/” relative to the working directory, the SQLite database can be referred to by name (a character string) with the argument db. A connection to the SQLite datbase will be opened internally within the function call, the SQLite database will be queried, and then the connection closed. Alternatively, a SQLite database stored anywhere on the disk drive can be accessed by specifying the full filepath (character string) with the argument db_filepath.

This workflow is advantageous as it avoids hard file paths which beneficial if wanting to move your code onto another computer system. Furthermore, once codelists and the SQLite database have been created and stored in the appropriate folders, they can simply be referred to by name, resulting in an easier workflow. The function create_directory_system() will create the directory system required to use rcprd in this way. To avoid repetition of the previous section, this is showcased just once using the extract_ho function. For the sake of this example, we start by setting the working directory to a directory called inst/example within rcprd. To maintain the new working directory across multiple R markdown code chunks, we use knitr::opts_knit$set. To follow this section, the user should simply set their working directory as usual using setwd().

Next, the create_directory_system() function can be used to generate the required directory structure within the working directory:

An SQLite database called “mydb.sqlite” is then created in the “data/sql” directory, using the same data from the previous examples:

Finally, a code list called mylist.csv is created and saved into the codelists/analysis/ directory.

The mydb.sqlite database can now be queried to create a ‘history of’ type variable using the codelist mylist.csv, with the output saved directly onto the disk drive.

Note that in order to run extract_ho here, a connection to the SQLite database did not need to be created, the codelist did not need to be in the R workspace, and there is no output from this function. Instead the extracted variable has been saved onto the disk drive in an.rds file, and can be read into the R workapce in order to create an analysis-ready dataset using:

3.3.5 Extracting longitudinal data/time varying covariates.

All of the functions in section 3.3.1 and 3.3.2 have the option to extract data at a given time point post index date (specified through the t argument). This allows users to extract data at fixed intervals, which can be utilised for longitudinal analyses where time-varying covariates are required. If saving the extracted variables directly to the disk drive (out_save_disk = TRUE), the time at which data was extracted from, t, will be added to the file name by default.

3.3.6 Working with linked data.

This worked example has overlooked how to work with linked data. Linked data can be added to the SQLite database. The primary diagnosis file can be added using the add_to_database() function by specifying filetype = “hes_primary”. Any other linked file can be added to the SQLite database by writing a user-defined function which reads in the text file and formats the variables appropriately and specifying this through the `extract_txt_func` argument. However, the user will have to define functions for querying the linked data and creating variables for analysis. Working with linked data has not been made part of the core functionality because it is often much smaller in size, and files (e.g., HES Admitted Patient Care primary diagnosis file, or Office for National Statistics death registration data) are not broken up into large number of smaller files. This means they can be more easily read into R and dealt with in the R workspace.