How Do I Upload Data to Stata

Tabular array of Contents

Introduction

Most (sane) researchers probably utilize Redcap to collect and store their valuable inquiry data. The advances of using REDCap are plentiful: Reproducibility, automatic backups, data safety, GCP-compliant logging, etc. If yous still organize your hard-earned data in an Excel spreadsheet (yikes!) then return hither when yous have seen the REDCap light.

While REDCap is astonishing, is does has 1 minor disadvantage. Everytime you add a new record, correct an mistake or change annihilation in REDCap, you demand to download your data again to your computer earlier you lot can re-run your assay. And since each file you export from REDCap has a new an unique proper name, you likewise to update your Stata exercise-file. This my friend is repetitive and ho-hum monkey work. And nobody likes monkey piece of work.

Luckily, you tin can automate the process of downloading data direct within Stata. All it takes is five minutes and a few lines of code in your practise-file.

TL;DR

If y'all are the impatient "I saw Harry Potter but never read the books"-type, so yous tin can download the Stata do file here. If y'all are the type that want all the nitty-gritty details, and so read on.

What is API?

API stands for Application Program Interface. Information technology is basically a programming language that computer programs use to communicate with each other.

Let'southward utilise the Facebook app as an instance. What happens when y'all cheque your newsfeed?:

The app sends an API request via the cyberspace to Facebook's server along with a confidential API cardinal or password. The API request is a description of what data the app wants the server to send. The API key is a unique countersign that lets the server know who you are and what data you lot should have access to.
One time the server has confirmed who you are, it processes the request to discover the data you need.
The server then sends back an API response, containing the data that the app requested. In this case, the response would incorporate your notifications, the latest cat pictures on your newsfeed, and tons of ads.

Yous can besides picture show the API as the waiter on a eatery. You request some food, the waiter carriers the asking to the kitchen, the kitchen process the request, and sends the waiter back with a response in form of a squeamish enchilada.

Plenty with the theory. Time to put it to utilize.

Setup

In REDCap, the API is deactivated by default for security purposes, so you need to active it.

REDCap settings

To enable API, navigate to "User Rights", click on your username, click "Edit user privileges", tick "API Consign" and save changes:

But the project administrator can give you permission to use API. If you are project administrator but cannot find API in settings, so your institution probably restricted admission to APIs and you demand to contact the institution'southward REDCap super admin.

Once y'all has admission, update the folio. You should encounter a new "API" app in menu to the left:

If y'all see this, then the API is agile.

Note: Unless you lot know what y'all are doing or yous are just playing around with a examination database, I advice strongly confronting checking the "API Import/Update". If y'all fool effectually with the API import office, you can in theory overwrite your entire database.

Getting the API key

Click the API card. Click "Generate API token"

A 32-character text string appears. This is your API key (or API Token in REDCap lingo).

We need to salvage it for later. Open Notepad (or TextEdit for MacOS), re-create/paste the API token and save it every bit "APIKEY_redcap.txt" in your working folder:

A word of warning: The API key is confidential! Anyone with the API key tin download your entire dataset including social security numbers and other patient identifiable information. Treat your API primal as you would treat your pin lawmaking to your credit carte.

gyre

We will use the nifty picayune program, coil, to communicate with the API. gyre is short for Client URL. It is program used for interacting webpages and servers with commands instead of using mouse and keyboard.

Information technology should be preinstalled on Windows 10 and newer versions of MacOS. To check if it is installed, first open up the terminal by pressing the Windows-key and search for "Command Prompt" (in MacOS: press Cmd + spacebar and search for "Terminal"). Once in the concluding, type curl --5 (MacOS: curl –version). If the output looks something like this, you're good to become:

If cURL is not installed, follow this guide.

In Stata

Time to open Stata. We will employ Stata's shell function to run gyre and interact with the REDCap API. You can copy/paste the step-past-footstep guide below into your own do file or download the entire code here.

Define settings for roll

Kickoff we need to supply the settings that cURL needs to communicate with REDCap:

          ***** Redcap_API.practice ***** version fifteen articulate file shut _all set more than off  *** Import hole-and-corner API key file open up text using APIKEY_redcap.txt, read text // Txt file containing API cardinal file read text token // Store API key every bit local 'token'  *** cURL Settings (update to match your system and REDCap) local curlpath "C:/Windows/System32/curlicue.exe" // Folder where scroll is installed (MacOS: "/usr/bin/roll") local apiurl "https://redcap.au.dk/api/" // Link to REDCap database local outfile "redcap_export" // Name of the output file

The first part imports the underground API cardinal from the text file nosotros saved previously. Nosotros could also just add together the primal directly in the do file by typing local token = "1234567890ABCDE...". Simply if yous did that and shared your practise file or log with somebody, you lot've had to think to remove the API key beforehand. Storing the API key in a separate file is safer. You've wouldn't go along your pin code in your wallet next to your credit card either, right?

The second function stores the location where ringlet is installed and the link to your institution's REDCap database. Update both curlpath and apiurl to match your system and REDCap database. You can also change the name of the final output file if you lot want.

Note: Stata deletes the content of all locals as presently as it has run the practice file until the terminate. Therefore, you lot demand to run the entire exercise file from the top to lesser for this code to work. Otherwise, Stata will but throw angry carmine errors in your face.

Run curl

At present information technology is time to run cURL:

          *** Run scroll command shell  `curlpath' 	/// 	--output `outfile'.csv 		/// 	--grade token=`token'	/// 	--course content=record 	/// 	--form format=csv 		/// 	--form type=flat 		/// 	`apiurl'

The code might seem a bit complex, merely basically beat out is Stata'south way of request Windows (or MacOS) to run a program on the compute. In this instance, the programme is whorl.exe, which nosotros previously stored in the local curlpath.

The next two lines specify that whorl should relieve the output with the proper noun stored in the local outfile and that the API key is found in the local token, which we both divers higher up.

The four lines beginning with --form specify what kind of data we want. In this case, nosotros want to download all records (token=tape), we want the data in csv format (format=csv), and each ascertainment should be on a divide row (blazon=apartment).

The terminal line specify that REDCap tin be found in URL provided in the local apiurl.

Now, run the entire do file. A window should pop up, and after a few seconds, information should appear as a csv file in your binder. Accio data. It'southward freaking magic!

Import to Stata and add value label

You lot've downloaded the information but it is still in a "raw" csv file. Y'all'll want to convert it to Stata format and then add all those lovely value labels that you spent and then much time writing in your REDCap database.

Importing commencement:

          *** Catechumen CSV file to Stata format import delimited `outfile', /// 	bindquote(strict) /// Fix quotes in text fields 	stringcols(two) // Import second variable every bit string (Optional. Relevant if 2d var is an ID var with leading zeroes such as SSN, CPR or like study ID) erase `outfile'.csv // Delete csv file

If you lot've used import delimited before, this part should exist pretty straight forward. Note, that in my databases, the second variable is usually some unique report ID like a social security numbers. Since these IDs often contain leading zeroes ("0123456789"), I forcefulness Stata to import the second variable as a string with the option stringcols(two), then Stata doesn't recollect that they are numbers and remove the offset zip ("123456789"). To proceed my computer neat'due north tidy, I besides ask Stata to erase the csv file.

Last thing nosotros need is to add the value labels from REDCap. You'll need to download the labels manually from REDCap:

Correct-click the Stata logo and select Save File As

Relieve in working binder and rename it RedcapValuelabel.exercise

Open up RedcapValuelabel.do and delete the first 7 lines of lawmaking, so it doesn't interfere with our do file:

Save the changes and go back to your main do file. Annotation, that yous need to do this every fourth dimension you make changes to your REDCap database.

At present you but demand to add a few lines of code to add the value labels and salve the concluding output:

          ** Employ value labels (Optional). do RedcapValuelabel.do, nostop  ** Salve save `outfile'.dta, replace

The nostop selection forces Stata to run the do file until the end, fifty-fifty if it encounters an error along the way. While this selection is by and large non recommended, information technology is practical in this specific setting, since y'all might later ask coil to only download specific variables from REDCap (See below). If you were to only download some specific variables (included and disease for example), then RedcapValuelabel.do would produce an error and finish, when trying to characterization all the other variables that haven't been downloaded. Nostop prevents that.

And at present you are done! Congratulations on becoming your office's resident API magician. Hopefully, it all worked similar a amuse. Encounter the sections beneath for troubleshooting and tips on other API features.

Troubleshooting

If the code doesn't piece of work, make sure you've specified the correct apikey and apiurl, and that you run the entire do file from height to bottom. If that doesn't do it, endeavor downloading my do file from Github and encounter if you've made a typo. If it still doens't piece of work, then don't hesitate writing to me.

Download specific records with filter logic

If you just need specific patients (records), y'all can utilize REDCap's filter logic to specify which records the API should to download. This can make downloading a lot faster if yous have a large database. For case, you lot might merely need the patients that were included in your written report (included=1) and got the disease you are interested in (disease=i). To select these records, you lot only need to add a unmarried line to the trounce command:

          shell  `curlpath' 	/// 	--output `outfile'.csv 		/// 	--form token=`token'	/// 	--grade content=record 	/// 	--grade format=csv 		/// 	--form type=flat 		///   --grade filterLogic="[included]='ane' and [disease]='1'"   /// 	`apiurl'

Download specific variables

Similarly to the instance in a higher place, y'all might not want to download all variables. This could be relevant if simply desire inclusion status and illness condition merely don't want to download patient identifiable data like social security numbers etc. Y'all can then specify the variables you want to download past adding the --grade fields[]=varname option:

          beat out  `curlpath' 	/// 	--output `outfile'.csv 		/// 	--class token=`token'	/// 	--form content=record 	/// 	--class format=csv 		/// 	--form type=flat 		///   --form fields[]=included	///   --form fields[]=illness	/// 	`apiurl'

There are tons of options like this. Check sources for more inspiration.

Sources

Most of this code is heavily inspired by (i.e. re-create-pasted from) this magnificent code posted on Github. It is written past "Luke" who appears to be REDCap's version of Dumbledore. Thanks Luke!

He has also writting a bunch do files for doing more complex stuff with the API that you should check out, if you want to download metadata, download specific information forms, reports etc, or if y'all want to learn how to upload data.

atkinsandas1947.blogspot.com

Source: https://www.ebbehoej.dk/post/automating-data-import-from-redcap/