Exploring variables in OpenSAFELY: the ehrQL Variable Explorer

OpenSAFELY researchers use a query language called ehrQL¹ to transform raw electronic health records into research-ready datasets. A dataset is a file consisting of one row per patient, and columns detailing features of interest about each patient. These columns are also called variables. A variable can be just about anything required for a statistical analysis, ranging from common demographic variables such as age, sex and ethnicity, to more complex definitions like “number of medications received in the 12 months prior to first stroke”.

Different studies will often use similar variables. All OpenSAFELY code is publicly available² enabling researchers to find examples of best practice and reuse variables where appropriate. However, we didn’t have an easy way to look across studies and understand how variables were being created and/or reused by our users in practice.

As part of Team Research Software Innovation’s (RSI) work, we wanted to explore whether there might be opportunities to make creating - and particularly reusing - variables easier. What kinds of variables do researchers create? Are there variables that appear repeatedly across studies? How much variation exists across similar variables? And what does that tell us about where standardisation or reuse could be useful?

Answering those questions was easier with a method of exploring the use and reuse of variables across all OpenSAFELY studies. Enter the ehrQL variable explorer. Feel free to take a look, but bear in mind that it is a snapshot in time and not something we plan to regularly update.

The landing page shows us a table (figure 1) of every ehrQL variable in every OpenSAFELY study, the repository (project) it is found in, and a link to the exact line of code where it is defined. Figure 2 shows what we see if we click on the first “age” variable in the table.

Figure 1 - landing page with table of all ehrQL variables in all OpenSAFELY studies

Figure 2 - clicking the link in the above table takes you directly to the line where the age variable is defined in GitHub

We can also search for variables by name (Figure 3)

Figure 3 - typing “smok” in the filter box shows there are 16 variables with 8 different names in all OpenSAFELY projects related to smoking

There is a checkbox to group together identical variable names. Variables may share a name, but have a different implementation - we call these variants. So for example, in the figure below, the variable “sex” occurs 57 times across 35 repos (projects), and there are 5 distinct variations on how it is defined. Once we’d identified variations, we were then able to investigate further to understand how much of this variation was meaningful, and how much could potentially be standardised.

Clicking a row shows the individual variables in the group along with the variant and again the link directly to where the variable is defined. In the example below we see that there are 9 variants of the variable “ethnicity” and while there are different variants, the two studies disease_incidence and inflammatory_rheum both use the same definition. This helped us identify instances where variables were reused as-is.

Another checkbox allows us to group together items where the variable definition is the same, but the name is different. E.g. for “sex” we find that there are variables called sex_pc, sex_sus and cov_cat_sex, but which have the exact same definition as many of the other “sex” variables.

The final option allows “fuzzy” grouping, where differences like dates, value ranges and clinical codes are ignored. This helps us discover very large groups of variables which only differ by the choice of clinical codes. I.e. the variables defined as “find all records matching this list of ckd codes, group by patient, sort by date, and select the earliest date” and “find all records matching this list of diabetes codes, group by patient, sort by date, and select the earliest date” - are essentially the same variable, only differing by choice of codelist.

The fuzzy grouping was really useful for us, because we were able to start thinking more about whether there were particular structures or logic that were reused across variables, even if the clinical topic was different. Through this, we managed to identify about five structures that were reused in lots of different contexts, but which weren’t immediately obvious without this tool.

Creating this tool was a relatively low-effort way to learn a lot about the types of variables that are created and used in OpenSAFELY, how they do and don’t vary, and what reusable elements exist. We found a few instances where variables are defined very similarly, with some variation, and many more where structures and logic followed similar patterns despite the variables being quite different. We can now use what we’ve learned to explore where there might be opportunities to make creating and potentially reusing variables easier for researchers, as part of a broader piece of work we’re currently doing.

As always, if you have any questions, please get in touch: team@opensafely.org