{"id":832,"date":"2021-06-29T15:03:50","date_gmt":"2021-06-29T19:03:50","guid":{"rendered":"https:\/\/blog.uvm.edu\/tbplante\/?p=832"},"modified":"2024-07-01T15:49:38","modified_gmt":"2024-07-01T19:49:38","slug":"part-4-defining-your-population-exposure-and-outcome","status":"publish","type":"post","link":"https:\/\/blog.uvm.edu\/tbplante\/2021\/06\/29\/part-4-defining-your-population-exposure-and-outcome\/","title":{"rendered":"Part 4: Defining your population, exposure, and outcome"},"content":{"rendered":"\n

Importing and merging files<\/h2>\n\n\n\n

Odds are that you will get raw data to work with that is in a CSV, sas7bdat, Excel’s xlsx file format, or something else. Stata can natively import many (but not all) file types. The simplest thing to do is use the –import– command then immediately save things as a temporary Stata .dta file and later merge them. <\/p>\n\n\n\n

Importing commands differ by filetype. Type –help import– for details. But here are the 3 commands I use most frequently. These assume that the files are in the present working directory, which you can see by typing –pwd–. Remember to open Stata in Windows by double-clicking the .do file of interest in Windows explorer to set the folder that the .do file is sitting in as the working directory. <\/p>\n\n\n\n

\/\/ CSV aka comma separated value, importing variable names\n\/\/ from the first row\nimport delim using \"NAME.csv\", clear varnames(1)\nsave \"name_csv.dta\", replace \/\/ save as Stata dataset\n\n\/\/ sas7bdat:\nimport sas using \"NAME.sas7bdat\", clear case(lower)\nsave \"name_sas.dta\", replace \/\/ save as Stata dataset\n\n\n\/\/ Excel, MAKE SURE THAT YOU DON'T ALSO HAVE THE FILE\n\/\/ OPEN IN EXCEL or it won't import it. This will import\n\/\/ variable names from the first row.\nimport excel using \"NAME.xlsx\", clear firstrow.\nsave \"name_xlsx.csv\", replace \/\/ save as Stata dataset<\/code><\/pre>\n\n\n\n
There are lots of fancy settings within these commands.<\/p>\n\n\n\n
To merge, simple merge all of your new .dta files using the –merge– command. This assumes that all files have a variable named “id” that uniquely identifies all rows and is preserved across files. eg:<\/p>\n\n\n\n
use name_csv.dta\nmerge using name_sas.dta\ndrop _merge\nmerge using name_xlsx.dta\ndrop _merge<\/code><\/pre>\n\n\n\n
The merge command generates a “_merge” variable that tells you where a specific variable came from. Review this variable and the output in Stata very closely<\/em>. You need to drop the “_merge” variable before merging other datasets. <\/p>\n\n\n\n
Getting the population, exposure, and outcome correct in your analytical dataset, and being able to come back and fix goofs later<\/h2>\n\n\n\n
Defining a study population, exposure variable, and outcome variable is a critical early step after determining your analysis plan. Most epidemiology projects come as a huge set of datasets, and you’ll probably need to join multiple files into one when defining your analytical population. Defining your analytical population is an easy place to make errors so you’ll want to have a specific script that you can come back and edit again if and when you find goofs.<\/p>\n\n\n\n
For the love of Pete — Please generate your population, exposure, and outcome variables using a script so you can go back and reproduce these variables and fix any bugs you might find!<\/em><\/strong><\/p>\n\n\n\n
When you make these variables, you’ll likely need to combine several datasets. This will require mastery of importing datasets (if not in the native format for your statistical program) and combining datasets. For Stata, this means using –import– and –save– commands to bring everything over into Stata format, and then using –merge– commands to combine multiple datasets. <\/p>\n\n\n\n
Make a variable for your population that is 0 (not included) or 1 (included)<\/h3>\n\n\n\n
One option in generating your dataset is to drop everyone who isn’t in your dataset. I recommend against dropping individuals who aren’t in your dataset. Instead, create a variable to define your population. Name it something simple like “included”, “primary population”, “group_a” or whatnot. If you will have multiple populations (say, one defined by prevalent hypertension using JNC7 vs. ACC\/AHA 2017 hypertension thresholds), then you should have a variable for each addended with a simple way to tell them apart. Like “group_jnc7” and “group_aha2017”. <\/p>\n\n\n\n
Useful code in R and Stata to do this:<\/p>\n\n\n\n
    \n
  • Count <\/li>\n\n\n\n
  • Generate and replace (Stata), mutate (R)<\/li>\n\n\n\n
  • Combine these with assigning single equals sign “=” (Stata & R, I say out loud “assign” when using this) and “<-" (R)<\/li>\n\n\n\n
  • use  –if–, –and–, & –or– statements<\/li>\n\n\n\n
  • Tests of equality: >, =, <=, != (not), == ("equals exactly"), not single equal sign<\/li>\n<\/ul>\n\n\n\n
    Example Stata code to count # of people with diabetes, generate a variable for group_a and assign someone to group_a if they have diabetes. <\/p>\n\n\n\n
    count if diabetes==1\ngen group_a=0\nreplace group_a=1 if diabetes==1\ncount if group_a==1 <\/code><\/pre>\n\n\n\n
    When creating a new variable from another variable, sometimes it’s helpful to start to generate a missing variable first (a dot) then replace a certain group as 0 and another as 1. This is especially helpful for strings. Strings must be in quotations, btw. Here’s an example of how to make a numeric variable from “Y” and “N” strings<\/p>\n\n\n\n
    gen diabetes = . \/\/ start with variable that's missing for all\nreplace diabetes = 0 if diab_string == \"N\"\nreplace diabetes = 1 if diab_string == \"Y\"<\/code><\/pre>\n\n\n\n
    Missing as positive infinity in Stata<\/h3>\n\n\n\n
    Read up about missing values by typing –help missing–. In Stata, missing values are either a dot or a dot with a letter (e.g., “.” or “.a”). Mathematically, Stata considers a dot missing value to be positive infinity, and each dot and letter missing value to be even larger positive infinity, so:  one billion trillion < . < .a < .b < .c and so on. Understanding that positive infinity is considered missing in Stata is critically important<\/em><\/strong> when using greater than statements, since anything greater than another value will include all missing values. So, imagine you had a population of 1 million people and only 100 of them were asked how many popsicles they have in their freezer and half had 0 to 10 and the other half had 11 to 20. If you try to make a variable called “popsicle_count” that is 1 for the lower half (0-10) and 2 for the higher half (11-20),  you’d do something like this:<\/p>\n\n\n\n
    gen popsicle_count = . \/\/ everyone has a missing variable \nreplace popsicle_count=1 if popsicle <=10<\/code><\/pre>\n\n\n\n
    replace popsicle count = 2 if popsicle >10<\/code><\/pre>\n\n\n\n
    …you would the popsicle_count variable would have 25 people with a value of 1 and 999,975 with a value of 2. this is because the last line didn’t specify what to do with missing values. The easy workaround here is to use “& popsicle <." to specify that you wanted to include anyone with a value less than positive infinity, aka missing values, in the last line. The correct<\/em> way of writing this would be:<\/p>\n\n\n\n
    gen popsicle_count = . \/\/ everyone has a missing variable \nreplace popsicle_count=1 if popsicle <=10\n<\/code><\/pre>\n\n\n\n
    replace popsicle_count=2 if popsicle >10 & popsicle <. \/\/ NOTE!!<\/code><\/pre>\n\n\n\n
    This last line correctly ignores all variables when assigning a value of 2 since it applies the number of 2 to anything greater than 10 and less than positive infinity, aka anything less than a missing value. . <\/p>\n\n\n\n
    Here’s example R code to do the same (df=data frame).<\/p>\n\n\n\n
    nrow( df %>% filter(diabetes == 1) )\ndf = df %>% mutate(group_a = ifelse(diabetes == 1, 1, 0) )\n<\/code><\/pre>\n\n\n\n
    Make an inclusion flowchart<\/h3>\n\n\n\n
    These are essential charts in observational epidemiology. As you define your population, generate this sort of figure. Offshoots of the nodes define why folks are dropped from the subsequent node. Here’s how I approach this, folks might have different approaches:<\/p>\n\n\n\n