Importing and merging files

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.

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.

// CSV aka comma separated value, importing variable names
// from the first row
import delim using "NAME.csv", clear varnames(1)
save "name_csv.dta", replace // save as Stata dataset

// sas7bdat:
import sas using "NAME.sas7bdat", clear case(lower)
save "name_sas.dta", replace // save as Stata dataset


// Excel, MAKE SURE THAT YOU DON'T ALSO HAVE THE FILE
// OPEN IN EXCEL or it won't import it. This will import
// variable names from the first row.
import excel using "NAME.xlsx", clear firstrow.
save "name_xlsx.csv", replace // save as Stata dataset

There are lots of fancy settings within these commands.

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:

use name_csv.dta
merge using name_sas.dta
drop _merge
merge using name_xlsx.dta
drop _merge

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. You need to drop the “_merge” variable before merging other datasets.

Getting the population, exposure, and outcome correct in your analytical dataset, and being able to come back and fix goofs later

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.

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!

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.

Make a variable for your population that is 0 (not included) or 1 (included)

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”.

Useful code in R and Stata to do this:

• Count
• Generate and replace (Stata), mutate (R)
• Combine these with assigning single equals sign “=” (Stata & R, I say out loud “assign” when using this) and “<-" (R)
• use –if–, –and–, & –or– statements
• Tests of equality: >, =, <=, != (not), == ("equals exactly"), not single equal sign

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.

count if diabetes==1
gen group_a=0
replace group_a=1 if diabetes==1
count if group_a==1 

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

gen diabetes = . // start with variable that's missing for all
replace diabetes = 0 if diab_string == "N"
replace diabetes = 1 if diab_string == "Y"

Missing as positive infinity in Stata

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 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:

gen popsicle_count = . // everyone has a missing variable 
replace popsicle_count=1 if popsicle <=10

replace popsicle count = 2 if popsicle >10

…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 way of writing this would be:

gen popsicle_count = . // everyone has a missing variable 
replace popsicle_count=1 if popsicle <=10

replace popsicle_count=2 if popsicle >10 & popsicle <. // NOTE!!

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. .

Here’s example R code to do the same (df=data frame).

nrow( df %>% filter(diabetes == 1) )
df = df %>% mutate(group_a = ifelse(diabetes == 1, 1, 0) )

Make an inclusion flowchart

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:

• Node 1 is the overall population.
• Node 2 is individuals who you would not drop for baseline eligibility reasons (had prior event that discounts them or missing data to prevent assessment of their eligibility)
• Node 3 is individuals who you would not drop because you can assess them for necessary follow-up (incomplete follow-up, died before required follow-up time, missing data)
• Node 4 is individuals who you would not drop because they had all required exposure covariates (if looking at stroke by cholesterol level, people who all have cholesterol). This is your analytical population.

If you have two separate populations (eg, different hypertension populations by JNC7 or ACC/AHA 2017), you might opt to make two entirely separate figures. If you have slightly different populations because of multiple exposures (e.g., 3 different inflammatory biomarkers, but you have different missingness between the 3), you might have the last node fork off into different nodes, like this:

I generate these via text output in Stata then manually generate them in PowerPoint. To make these, I use a series of “sum” and “count” commands following along with “noisily display” commands all in a quietly loop. (Noisily overrides quietly for a single line. When debugging, you might want to hide the quietly loop.)

I also make an “include” variable that defines the analytical population(s) of interest.

You can display specific bits of data after a “sum” command, including r(N), which is the N of a sample. If you wonder what bits of data are available after a command like “sum if include==1”, type “return list”.

Example:

quietly {
gen include=1 // make a variable called "include" that is 1 for everyone
count if include ==1 // count the # of rows with an include variable equal to 1, this is everyone. It will save that value as r(N). 
noisily display "Original study population, n= " r(N) // you can print this
//
// now lets print out the people we exclude
count if prevalent_htn_jnc7==1 // this will count the # with prevalent htn to be excluded
noisily display " --> Hypertension at baseline, n= " r(N)
count if prevalent_htn_missing==1 
noisily display " --> Missing bp at baseline, n= " r(N)
//
// now we are going to replace the include variable as 0 for people missing the two things above
replace include = 0 if prevalent_htn_jnc7==1
replace include = 0 if prevalent_htn_missing==1
count if include==1
noisily display "normotensive at baseline, n= " r(N)
// and so on
}

If you are using weighted data, this approach will differ slightly. First, you will have to svyset your data. Next, you will use “svy, subpop(IF COMMAND HERE): total [thing]”. Instead of using “return list”, you use “ereturn list” to see the bits that are saved. The weighted N is e(N_pop), for example.

svyset [pweight=sampleweight]
gen include=1
svy, subpop(if include==1): total include 
ereturn list // notice e(b) is there, it's the beta from the prior estimation
noisily di "original study population, n= " e(b)[1,1]
// above prints out the first cell of the matrix e(b), hence the [1,1]
// type "matrix list e(b)" to see what's in that matrix. 
// now figure out how many are excluded for missing a biomarker
svy, subpop(if include==1 & biomarker==.): total include
// now print it out, but since this uses sampling, it will not be a whole number. Print out a whole number with the %4.0f formatting code. 
noisily di " --> missing biomarker, n= " %4.0f e(b)[1,1]
// now update the include to be 0 for missing biomarkers 
// and display the count of the node print the node
replace include=0 if biomarker==. 
svy, subpop(if include==1): total include
no di "not missing biomarker, n= " %4.0f e(b)[1,1]
// and so on

When you get to the end, you’ll have a variable called “include” that you will use in all of your later analyses to define your analytical population. Depending on your analysis, you might need to make a few different include variables. For example, we commonly run hypertension analyses using both jnc7 and acc/aha 2017 hypertension definitions, so I usually have an “include_jnc7” and also a separate “include_aha2017” variable.

Defining exposure and outcome

This seems simple, but define clearly what your exposure is and your outcome is. Each should have a simple 0 or 1 variable (if dichotomous) with an intuitive name. You might need 2 separate outcomes if you are using different definitions, like “incident_htn_jnc7” and “incident_htn_aha2017”.

Table 1

“Table 1” shows core features of the population by the exposure. Don’t include the outcome as a row, but include demographics and key risk factors/covariates for outcome (eg if CVD, then diabetes, blood pressure, cholesterol, etc.). Some folks include a 2nd column that presents the N for that row. Some folks also include a P-value comparison as a final row. I tend to generate the P value every time but only present it if the reviewers ask for it.

In Stata, I use the excellent table1_mc program to generate these, which you can read about here. If you are using p-weighted data, you can use this script that I wrote, here.

For R, I am told that gtsummary works well.

For lots more details on Table 1s, please continue to the next post, here.

Pin down your authorship list

Determine authorship list before you lift a finger on any analysis and get buy-in from collaborators on that list.

Stay organized

Have one folder where you save everything, and have subfolders within that for groups of documents. I suggest these subfolders:

• Manuscript
• Abstract
• Data and analysis
• Paper proposal (for REGARDS projects & others with manuscript proposal documents)

…and keep relevant documents within each one. You might want to put an “archive” folder within each subfolder (e.g., manuscript\archive) and move old drafts into the archive folder to reduce clutter.

Give documents a descriptive name. Don’t call it “manuscript [versioning system].docx”– use terms for your projects. If you are doing a REGARDS paper looking at CRP and risk of hypertension, name it “regards crp htn abstract [versioning system].docx”.

Use an intuitive versioning system. I like revision # then version # (eg r00 v01). Many people use dates. If you use dates to keep track of versions, append your documents with the ISO8601 date convention of YYYY-MM-DD. Trust me. Lots of details on this post.

Have realistic goals and stick to deadlines

Come up with some firm deadlines and do your best to stick with them. Here are some goals to accomplish in moving a project forward, if you wanted an example.

• Combine all existing written documents (eg, proposal) into one manuscript.
• Draft blank tables and decide what figures you want to make. Write methods.
• Generate baseline characteristics. Describe in results.
• Generate descriptive statistics/histograms for your exposure and outcome(s). Describe in results.
• Estimate primary and secondary outcome(s). Describe in results.
• Complete secondary analyses. Describe in results.
• Finish first draft. Send to your primary mentor or collaborator.
• Integrate feedback from primary mentor or collaborator into a second draft. Circulate to coauthors.
• Integrate feedback from coauthors into a document to be submitted to a journal.
• Format your manuscript for a specific journal and submit it. (This takes a surprisingly large amount of time.)

Managing your mentor: Send reminder emails more frequently than you probably realize

I block off time to work on your stuff, but clinical priorities or other professional/parenting challenges might bump that time. I try to find other time to work on your stuff, but a big crisis might mean that I don’t have a chance to reschedule.

Please, please, please, please email me early and persistently about your projects. This will never annoy me — these emails are very helpful. Quick focused emails are helpful here, especially if you re-forward your prior email threads. Eg, “Hi Tim, wondering if you had a chance to take a look at that draft from last week, reforwarded here. Thanks, [name].”

Working on revisions

Use tracked changes

And remember to turn them on when you send around a draft!

Append your initials to the end of the document that you are editing for someone else

For me, I’ll change a name to “My cool document v1 tbp.docx”.

Summer goals and expectations

Hi there! Thanks for expressing interest in working on an epidemiology project with me this summer. This project will entail:

• Using cohort study or clinical trial data trying to extend knowledge of cardiovascular disease (CVD).
• You getting your hands dirty with statistical coding (e.g., R or Stata).

My expectations for all LCOM summer students are:

• You have a computer that works and internet that is dependable enough to allow Zoom conferences. You don’t have to be in VT.
• In advance of the summer, you will submit a manuscript proposal to the cohort that will be used, and apply for funding through the CVRI or LCOM (typically due in February). If we need an IRB proposal (which we likely don’t since there’s probably an existing IRB), then you’ll lead the completion of that.
• We’ll meet weekly via Zoom for an hour or so during the summer to review the project’s progress. I’ll be available in between meetings via email, Zoom, or whatnot.
• If we’re working with a biostatistician and using R, you’ll meet with them to learn R pointers.
• You’ll complete the analysis, with help from me in learning the ins and outs of coding.
• If doing a REGARDS/LCBR-related project, you’ll attend lab meetings.
• At the end of the summer you will have: (1) A complete first draft of a manuscript and (2) a completed abstract that can be submitted to a conference.

Things to set up now.

Zotero

I use Zotero as a reference manager. It’s the bomb diggity. We will share references in a private group library that we can both edit. Only the people who have this library shared with them can see its contents.

To install Zotero, do the following:

1. A free Zotero account. Sign up here. Please tell me your username so I can start a group library with you.
2. Zotero’s desktop app. Make sure to log into your account in the desktop app. It’s under edit –> preferences.
3. The Zotero web browser plugin for your web browser of choice. You need to have the Zotero desktop app open for this to work.
4. The Zotero MS Word plugin (in Zotero desktop app, click Edit –> Preferences –> Cite –> Word Processors). This has been finicky with the specific MS Office install on the LCOM laptops so it might take some working to get it to work. But! It’ll work.
   • Here’s a link to help you install Zotero on a UVM LCOM laptop: https://www.zotero.org/support/word_processor_plugin_manual_installation

I’ll send you a shared library invitation. To accept the group library invite, do the following:

• Go to zotero.org, log in.
• In the top right, click on your username. A menu should drop down, click “inbox”.
• Accept the group library invitation.
• Open up the Zotero desktop app and let it sync (again, you need to be signed in on the desktop app, seen #2 above). The group library folder should appear in the left column all the way on the bottom.

Group libraries are awesome because we can compile references that either of us can insert into a document. Please keep the group library organized. If you add a new reference, please make a subfolder to stick it in so you don’t have to search for references one by one.

How to use Zotero

This is covered in a separate post, here.

Microsoft OneDrive

This is through LCOM. Not UVM, not your personal account.

1. Open the OneDrive on your computer and sign in with your LCOM credentials if you aren’t already.
2. I’ll share a research folder with you. You’ll need to sync it with your computer. To do that, go to onedrive.com, log in with your LCOM credentials (firstname dot lastname at med dot uvm dot edu).
   • After you log in, you’ll be on the landing page for OneDrive. Click “Shared” on the left column.
   • If you see a mix of files and folders, limit the view to just folders by clicking the icon of a folder to the right of “With You”.
   • Find the research folder and select it. On the top bar click “Add shortcut to My Files” (you might need to make the window full screen to see this option).
   • Allow the OneDrive desktop app to sync. Now all of the files should be available offline in the Onedrive folder on your computer.

Microsoft Word

Unfortunately, writing papers in Google Drive is a bit too onerous. Please download this manuscript file, which you will be using to draft your manuscript.

Your statistical software, option 1: Stata – note: this is what we are using for the 2024 summer session.

At this point you should know if you are using Stata or R. I use Stata. UVM has an institutional subscription to Stata. You can download and install it from the UVM Software page, here. For this you will log in with your UVM (not LCOM) credentials. To download it, hit the down arrow (1) then download. After it’s installed, you’ll need the serial number, code, and authorization to activate it. That’s under “more info” (2).

<– Two steps to install Stata from UVM

Your statistical software, option 2: R – we aren’t using it in the 2023 summer session.

R is a free and open source computer language intended for use in statistics. RStudio is a commercial software that makes using R much more user-friendly. I have only used R a few times and don’t have expertise in the language.

Research credentialing things

CITI training and directory modification

You need to complete the CITI training (both “human subjects training/IRB” and “GCP” trainings) in advance of starting the summer so you can be added to our human subjects IRB, see details at these links:

• https://www.uvm.edu/rpo/citi-program-training
• Use these dropdown menus to see if you did the human subjects training/IRB (first dropdown) and GCP trainings (third dropdown): https://www.uvm.edu/~irb/education/TutorialCOMPLETION.htm

And please modify your directory listing so you can be added to the IRB as described here: https://www.uvm.edu/sites/default/files/media/Students_-_How_To_Sign_Up_to_Do_Research_at_UVM_2.pdf

Once you have finished these steps, let me know so we can add you to the IRB. There is a delay between when we request you to be added to the IRB and when you are added to the IRB, fyi.

Cornerstone research training

Historically, students have been required to do an online research training session in Cornerstone by the end of the first week. Details from this come from the Student Services office. I don’t have details about how to complete this. IIRC this is specifically for students doing projects that involve interacting with patients or patient data at UVMMC, which isn’t what we are doing. But, look for those details coming your way.