help for cprdutil(Roger Newson)

Utilities for inputting Clinical Practice Research Datalink (CPRD) datasets into Stata

Input a set of CPRD XYZ lookups from text files in a directory, creating Stata datasets in another directory
and/or a do-file to create value labels:

cprd_xyzlookup , txtdirspec(directory) [ dtadirspec(directory) replace

dofile(filename [ , replace]) ]

Input a set of CPRD non-XYZ lookups from text files in a directory, creating Stata datasets in another directory:

cprd_nonxyzlookup , txtdirspec(directory) [ dtadirspec(directory) replace ]

Input a single CPRD non-XYX lookup from a single text file into memory:

cprd_batchnumber using filename [ , clear ]

cprd_bnfcodes using filename [ , clear ]

cprd_common_dosages using filename [ , clear ]

cprd_entity using filename [ , clear ]

cprd_medical using filename [ , clear ]

cprd_packtype using filename [ , clear ]

cprd_product using filename [ , clear ]

cprd_scoremethod using filename [ , clear ]

Input a single CPRD non-lookup dataset from a single text file into memory:

cprd_additional using filename [ , clear dofile(filename) nokey ]

cprd_clinical using filename [ , clear dofile(filename) ]

cprd_consultation using filename [ , clear dofile(filename) nokey ]

cprd_immunisation using filename [ , clear dofile(filename) ]

cprd_patient using filename [ , clear dofile(filename) nokey ]

cprd_practice using filename [ , clear dofile(filename) nokey ]

cprd_referral using filename [ , clear dofile(filename) ]

cprd_staff using filename [ , clear dofile(filename) nokey ]

cprd_test using filename [ , clear dofile(filename) ]

cprd_therapy using filename [ , clear dofile(filename) ]

Add observation-window variables to a CPRD patient dataset in memory, using practice variables from a practice dataset on disk:

cprd_patientobs using practice_datafile_name [ , accept ]

Input a single CPRD browser-export dataset from a single text file into memory

cprd_browser_medical using filename [ , clear nokey ]

cprd_browser_product using filename [ , clear nokey ]

Description

The cprdutil package is a suite of utility programs for inputting text datasets
produced by the Clinical Practice Research Datalink (CPRD),
and outputting Stata datasets and/or do-files to create value labels.
CPRD text datasets may contain XYZ lookup tables, non-XYZ lookup tables,
non-lookup datasets, or browser-export datasets,
with one text row for each of a set of things of a kind known to the primary-care sector of the British Health Service,
such as patients, primary-care practices, clinical events or prescriptions.
All of these may be output into Stata datasets,
with one output observation per input text row.
An XYZ lookup table may alternatively be translated into a set of Stata value labels,
created using a generated do-file,
to be assigned to variables in other Stata datasets after running the do-file in the dataset.
The cprdutil package uses the SSC packages keyby, addinby, lablist, chardef,
and intext,
which need to be installed for cprdutil to work.

Options for cprd_xyzlookup and cprd_nonxyzlookup

txtdirspec(directory) specifies an existing input directory,
assumed to contain text files with the extension .txt,
with 1 row of text for each entry in a lookup table.
These rows of text are keyed (uniquely identified) by the value of the first variable.

dtadirspec(directory) specifies an existing output directory,
to be populated by generated Stata datasets, one generated from each input text file,
with 1 observation per row of the input text file,
and with the same filename as the input text file
(apart from the extension, which will be .dta instead of .txt).
These observations will be keyed (uniquely identified), and sorted, by the value of the first variable.
This enables each dataset to be used as a lookup table,
with the first variable acting as a foreign key for use by the official Stata merge command,
or by the SSC command addinby.

replace specifies that any existing datasets in the output directory
with the same names as the generated Stata datasets
will be replaced.

Note that cprd_xyzlookups cprd_nonxyzlookups create multiple datasets in directories on disk.
Any pre-existing dataset in memory will be restored after this work is done.

Options for cprd_xyzlookups only

dofile(filename [ , replace]) specifies a do-file to be created,
with one command line for each code value for each XYZ lookup,
to create a value label for each of the XYZ lookups
if the do-file is executed in a non-lookup dataset.
These value labels can then be assigned to variables in the dataset.
The suboption replace specifies that any existing do-file of the same name in the same location
will be replaced.
An XYZ lookup table has 2 variables.
One is named code, and gives the numeric code for each table row.
The other has another name, and gives the value label for the corresponding value of code.
The dofile() option allows the user to translate the XYZ lookups into Stata value labels.

Options for commands creating datasets in memory from using files

clear specifies that any existing dataset in memory will be replaced.

dofile(filename) specifies the name of an existing Stata do-file,
usually created using the dofile() option of cprd_xyzlookups.
This do-file is executed by the dataset creation command,
and usually creates a set of value labels,
some of which are then allocated to variables in the generated dataset in memory.

nokey specifies that the dataset produced will not be sorted by a primary key of variables.
If nokey is not specified for a command that has a nokey option,
then the dataset will be sorted by a primary key of variables,
whose values will normally identify the observations uniquely,
with each value combination present in only one observation.
In browser-export datasets, the default key consists of a single variable,
such as a medical code named medcode or a product code named prodcode,
which identifies the observations uniquely.
The nokey option is useful in the case where the input text file has been produced
by concatenating multiple CPRD browser outputs,
which may contain some of the same medical or product code values.
The user may then add an extra variable (or variables) to the input text file,
to identify the browser output from which each observation comes,
input the input text file with a nokey option,
and then use the SSC package keyby to sort the dataset primarily by the extra variable (or variables),
and secondarily by the medical or product code.

Options for cprd_patientobs only

accept specifies that the observation-window entry and exit variables will be computed only for patients
for which the CPRD variable accept is equal to 1.
If absent,
then cprd_patientobs computes the observation-window entry and exit variables
for all patients with the necessary non-missing values
for the patient-specific and practice-specific date variables used to calculate the observation-window entry and exit variables.

For more information on the new variables created by cprd_patientobs,
refer to the section
New patient variables generated by the cprd_patientobs module
below.

Remarks

The Clinical Practice Research Datalink (CPRD) is a database of information
from primary-care practices in the British Health Service.
Tab-delimited text data files of various types can be retrieved,
containing data on things known to these practices,
such as practices, patients, staff members, clinical events, additional details on clinical events,
consultations, immunisations, referrals, tests, and prescribed therapies.
More information about CPRD and cprdutil can be found in a presentation by Newson (2018).

When a CPRD data retrieval is made, a set of lookup tables may also be provided,
also as tab-delimited text data files,
so the user can identify the meaning of the numbers in the datasets.
The cprdutil packages enables the user to translate the text lookup tables and datasets
into Stata datasets.
The lookups may be XYZ lookups, with 3-character names,
or non-XYZ lookups, with longer names.
The XYZ lookups typically have fewer data rows,
and only 2 variables (containing numeric codes and text labels),
making them suitable for translation into Stata value labels.
The non-XYZ lookups typically have more data rows,
and often have more than 2 variables,
making them suitable for translation into Stata datasets,
which can be merged into the non-lookup datasets,
using the merge command in official Stata,
or using the SSC package addinby.

CPRD data retrievals are usually made following a request to CPRD.
However, CPRD also provides a Web-aware browser program,
enabling the user to create lists of CPRD medical codes (identifying classes of medical events),
or lists of CPRD product codes (identifying classes of prescribed products).
The user may then export these lists to human-readable tab-delimited text files,
with one row per medical code or product code,
and columns describing the medical event or product.
These CPRD browser-export text files can then be input into Stata browser-export datasets in memory,
with one observation for each medical or product code in the list.

Stata datasets produced from CPRD data are sometimes (but not always) keyed by a primary key,
which is a list of variables by which the observations are sorted and uniquely identified.
The primary key is always present in lookup datasets, and contains only one variable.
It may or may not be present in non-lookup datasets, where it may contain more than one variable.
The presence of the primary key, and the variables contained, depends on the dataset type.
Datasets of non-keyed types may even contain duplicate observations,
with the same values for all variables.

Stata datasets created by cprdutil have variables corresponding to the tab-delimited columns of the input text file.
They may also have added generated variables.
For instance, in datasets with one observation per event,
the event date, and the date on which the event was logged into the CPRD system,
are provided by CPRD as the string variables eventdate and sysdate, respectively.
The Stata dataset will then also contain corresponding converted numeric Stata date variables,
named eventdate_n and sysdate_n, respectively.

The cprdutil package uses Roger Newson's SSC packages
keyby, addinby, lablist, chardef, and intext,
which need to be installed in order for cprdutil to work.
The user may download all Roger Newson's packages using the instasisay suite of do-files from Roger Newson's website.

The cprdutil package frequently produces a lot of output to the Stata log.
However, the user can prevent this by using the quietly prefix.

New patient variables generated by the cprd_patientobs module

These are added to a patient dataset in memory,
created using the module cprd_patient.
The new variables are created using practice observation-window date variables
merged in from a practice dataset on disk,
which was created using the module cprd_practice and then saved.
The new variables specify the dates and modes of entry and exit of the patient
to and from observation by CPRD.

Name Description

obscalc Observation window calculated

entrystat Entry status to observation by CPRD

entrydate Date of entry to observation by CPRD

exitstat Exit status from observation by CPRD

exitdate Date of exit from observation by CPRD

observed Patient observed by CPRD

The observation window is specified by variables calculated
if the necessary practice-specific and patient-specific dates are non-missing,
and (if accept is specified} if the patient-specific variable accept is also equal to 1,
indicating that the patient's data are of a quality acceptable to CPRD.
The variable obscalc is 1 if the observation window is calculated and 0 otherwise.
The variable entrydate specifies the date on which the patient entered into observation by CPRD,
and is the maximum of the date when the patient's practice started returning up-to-standard data,
the date when the patient most recently joined the practice,
and the earliest possible birth date for the patient.
The variable entrystat indicates which of these dates was the maximum,
and can be 0, 1, or 2 for practice up-to-standard date, patient joining date, and patient birth date, respectively,
with ties resolved preferentially in the order 2 > 1 > 0.
The vatiable exitdate specifies the date on which the patient exited from observation by CPRD,
and is the minimum of the date when the practice last sent data to CPRD,
the date when the patient left the practice,
and the patient's death date (if present).
The variable exitstat indicates which of these dates was the minimum,
and can be 0, 1, or 2 for practice data-reporting date, patient leaving date, or death date, respectively,
with ties resolved preferentially in the order 2 > 1 > 0.
The variable observed indicates that the patient was observed in an observation window
containing a non-zero number of days,
and is zero if obscalc is zero,
and otherwise is 1 if and only if entrydate <= exitdate, and zero otherwise.
Outside the observation window from entrydate to exitdate (inclusive),
a patient cannot be considered to be at risk of a CPRD event,
such as a therapy event or a clinical diagnosis.

Examples

These examples all assume that the CPRD text data are stored in a folder ../cprddata,
with subfolders /Lookups, /Data and /Browserexports,
containing the lookups, the non-lookup data, and the browser exports,
respectively.

The following commands produce a do-file xyzlookuplabs.do to create Stata value labels
corresponding to the XYZ lookups,
a set of XYZ lookup datasets in the directory ./xyzlookupdta,
and a set of non-XYZ lookups in the directory ./nonxyzlookupdta.
Note that all the directories must already exist for these commands to work.

. cprd_xyzlookup, txtdir("../cprddata/Lookups/TXTFILES") dtadir("./xyzlookupdta") replace dofile(xyzlookuplabs.do, replace)

. cprd_nonxyzlookup, txtdir("../cprddata/Lookups") dtadir("./nonxyzlookupdta") replace

The following command creates a CPRD practice dataset in memory,
using a text data file named practice.txt.
Value labels for the new dataset in memory are created using the do-file xyzlookuplabs.do,
which was produced by the previous commands.

. cprd_practice using "../cprddata/Data/practice.txt", clear dofile(xyzlookuplabs.do)

The user can save the dataset created in memory to a disk file, using save.
It is also possible to produce patient datasets, clinical-event datasets, and other kinds of CPRD datasets in this way.

The following command creates a non-XYZ lookup dataset in memory
from the lookup file medical.txt in the subfolder /Lookups,
with 1 observation per CPRD medical code specified by the variable medcode,
and data on the Read codes and verbal descriptions of these CPRD medical codes.

. cprd_medical using "../cprddata/Lookups/medical.txt", clear

The cprdutil package frequently produces a lot of output to the Stata log.
However, the user can prevent this by using the quietly prefix.
For instance, the previous example could have been written as follows:

. quietly cprd_medical using "../cprddata/Lookups/medical.txt", clear

This would have produced no output to the Stata log.
This capability might be useful if the user is mass-producing a large number of gigabyte-sized CPRD datasets
for a large research study.

The following command sequence creates disk-file Stata datasets in the directory ./nonlookupdta,
using text data files in the /Data subfolder
and the do-file xyzlookuplabs.do created in the first example.
First, we create a practice dataset in practice.dta.
Then, we create a patient dataset in patient.dta,
with added patient observation-window variables,
computed using the cprd_patientobs module
to input practice information from practice.dta.
We then tabulate, summarize and plot the patient observation-window variables.

. cprd_practice using "../cprddata/Data/practice.txt", clear dofile(xyzlookuplabs.do)

. save "./nonlookupdta/practice.dta", replace

. cprd_patient using "../cprddata/Data/patient.txt", clear dofile(xyzlookuplabs.do)

. cprd_patientobs using "./nonlookupdta/practice.dta", accept

. save "./nonlookupdta/patient.dta", replace

. tab accept obscalc, miss

. tab observed obscalc, miss

. tab entrystat obscalc, miss

. tab exitstat obscalc, miss

. summ entrydate, detail format

. summ exitdate, detail format

. scatter exitdate entrydate || line entrydate entrydate, sort || , by(observed) legend(row(1))

The following command sequence uses the cprd_browser_product command
to input a CPRD browser-export text file in the /Browserexports subfolder
and produce a CPRD browser-export dataset on disk in the directory ./browserexportdta,
with 1 observation per product code appearing in the text file.
We then use the command cprd_therapy to input a CPRD therapy text file
in the /Data subfolder,
producing a CPRD therapy dataset in memory,
and then use the SSC package addinby
to drop all therapy records with product codes not matched in the browser-export disk dataset.
Finally, we tabulate the frequency distribution of the other product codes,
which are matched in the browser-export disk dataset.

. cprd_browser_product using "../cprddata/Browserexports/myprodcodes.txt", clear

. list, abbr(32)

. save "./browserexportdta/myprodcodes.dta", replace

. cprd_therapy using "../cprddata/Data/therapy.txt", clear

. addinby prodcode using "./browserexportdta/myprodcodes.dta", unm(drop) keep(prodcode)

. describe, full

. tab prodcode, miss

Acknowledgments

I would like to thank Sophia Amjad and Sarah Cousins of CPRD Enquiries
for providing me with simulated sample CPRD-format libraries of text datasets,
complete with a full set of lookups and documents.
I found these sample libraries to be very useful when validating cprdutil.

Author

Roger Newson, Imperial College London, UK.

Email: r.newson@imperial.ac.uk

References

Newson, R. B. 2018. A fleet of packages for inputting United Kingdom primary care data.
Presented at the 2018 London Stata Conference, 8-9 September, 2018.

Also see

Help: Clinical Practice Research Datalink (CPRD)

keyby, addinby, lablist, chardef, intext if installed