Stata Friendly Excel File of US Foreign Assistance Data (Economic), 1990-2011

With the new push to for open government, more data has become publically accessible (while perhaps always available for the crafty researcher) on US foreign assistance.  In particular, the USAID Greenbook which includes historical-dollar and constant-dollar data on US foreign assistance loans and grants is available here

The data is broken down into economic and military assistance.  Unfortunately, the coding in Excel makes it a bit tricky to convert this data into long-form for time-series analysis in Stata.  If you are only interested in the line totals, a code for converting this and adding Correlates of War country codes is available on Steven Miller’s blog (Clemson University).  From there you can dump the Excel data into Stata by simply coping into the data editor.

But what if you want to keep all the different categories of aid?  I’ve uploaded an initial Excel file here which includes all of the data for economic assistance from 1990 to 2011 in long-form for 2011 constant dollars. (This might seem strange, when it would be just as easy to do all the country years, but the initial file was created for a colleague who is only interested in the 1990-2011 time period).

After downloading, this data can be copied into the Stata Data Editor.  I would upload the Stata file itself, but WordPress is doesn’t recognize .dta or .do files.

The rest of the data, including economic constant dollar assistance from 1946-1989, historical dollar economic assistance data 1946-2011, and both constant and historical dollar military assistance data 1946-2011 will be uploaded soon.

These data are open access from the US government and the Stata versions are provided publically here.  However, acknowledgements in any research projects that utilize these datasets is greatly appreciated.  

For those who are interested in how I recoded this – because you have a similar data problem, can’t wait for me to finish all the recoding, and/or don’t trust me and want to convert it yourself – instructions are below and a Word version of my Stata do file is has been uploaded along with the initial data.

Converting USAID Greenbook Data for Economic Assistance into a Stata Long-Form File

  1. Download the data file from  Note that you should download the disaggregated file for “Economic Assistance, 1946-2011 Constant-Dollar Data” near the bottom of the page.
  2. Clean up the spreadsheet. 
    1. In the second tab, delete the first six rows, which contain the title and notes.
    2. Select the entire worksheet and set the number format to “General”. This will remove all the commas from the numeric data, preventing Stata from converting to string format.
    3. Select the entire worksheet again. In the Data menu, select Custom Sort and sort by the Program Name column (note: if the columns are listed as letters rather than their variable names, select the box “My list has headers” in the top right corner).   Now the data is listed by country program.
  3. Generate separate Stata files in long-form for each program area of aid.
    1. Begin by selecting the header row and all the rows corresponding to Child Survival and Health (1-108).  Copy these.
    2. Paste the copied data into an empty Data editor in Stata, selecting “Variable Names” when prompted about whether the first row of data is data or variable names.
    3. Convert the data into long-form using the reshape command in Stata:
. reshape long fy, i(countryname) j(year)
      1. rename the new fy variable the name of the aid program and drop the programname variable, for example:
. rename fy childhealthsurvival
. label var childhealthsurvival "Child Health and Survival"
. drop programname
. order year

ii. save the data as a new Stata file, for example:

. save "childhealth.dta", replace

iii. Repeat all of Step #3 for all 21 categories of economic aid.

4.  Merge data files into a single dataset.

    1. Open each of your data files and make sure they are sorted by country-year.  For example:
. use "childhealth.dta", clear 
. sort country name year 
. save “childhealth.dta”, replace

B. Open the first data file you created and merge this with each of the 21 files you created in Step #3 using the Stata command merge.  You should have in total 20 merges (21-1(master file) = 20). For example:

. use "childhealth.dta", clear 
. merge countryname year using “dodsec.dta"

C. After each merge, delete the _merge variable that Stata generates.  Then sort by country-year again before merging the next file.  Note: in reality it doesn’t matter which order you merge the data in or which data you use as the “master file”.  But going in the same order as you created them will help keep track and ensure you do not miss a file.  

. drop _merge
. sort countryname year

D. Save your new US Economic Assistance Data, 1946-2011, constant dollars.

5.  Generate a row total for each country year.  (Optional)

A. If you are interested in row totals, i.e. the total aid a country received in a given year, generate a new variable for this using egen.  For example:

. egen econtotal = rowtotal(childhealthsurvival dodsecurity deva
ssist ecosecfund fooded globalhealth globalaids inactive migrati
on mcc narc terror othergrant otherfood otherstate otherusaid ot
herusda peacecorps title1 title2 multilat)



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s