The Stata Project-Oriented Guide

Introduction

2016-01-01T08:00:00.006+02:00

This blog is a free Stata tutorial. I have been using Stata for the last two years now for different applied work in economics and other fields of the social sciences. If you are in your undergraduate or graduate studies or if you are working for some agency that performs social research, you will probably need to use Stata in the context of your project. Stata has an extensive manual which is very accessible, in my opinion, but in order to know how to use it, one needs to already know the commands' names.

However, if you are new to Stata, and you have a project to do, there is a sequence of actions you probably need to do. This tutorial is constructed to follow this sequence: data assembly and construction of additional variables. Then I deliberately skip talking about commands that perform statistical analyses and leave it to your statistics or econometrics courses. But the second part of the tutorial (steps #5-#8) are dedicated to automating those commands and the creation of tables which will report the results. In addition, there are best practices of how to write code that will be easy to follow and change if needed.

I am assuming the reader has basic knowledge of Econometrics (regressions etc.) and I will not get into issues of how to specify an appropriate model. I will concentrate, though, on the practical steps one needs to do before and after the regressions, and how to organize the code so as to minimize mistakes.

The tutorial is divided to steps. You might not need to go through all the steps, so feel free to move on if you see the step is irrelevant for you. You can also navigate through the tutorial with the labels on the left bar. They consist of keywords (like an index) and steps numbers (like a table of contents).

The steps are as follows (keep in mind that the tutorial is still under construction):

Step #1: Getting the Data - if your Data isn't in .dta format - Excel as an example
Step #2: Combine Multiple Datasets into One - for datasets already in .dta format
Step #3: Simple Data Manipulation - generate variables, change values and drop variables or observations
Step #4: Thank God for the egen Command - a very powerful command that extends the possibilities of data manipulation.
Step #5: Keeping commands' calculations - How to tell your program to use the output from reg, sum, etc.
Step #6: Automation - macros, loops, and other sorts of fun

Step #7: Exporting Results to a Spreadsheet - Excel as an example

Step #8: Program Definition - if you start to see the same code in many .do files, maybe you should read this step.

I did not find time to fill in steps #7 and #8 here. For step #7, and other good things, I prepared some slides for a short Stata sequence I gave at the department. You can find them here.

Good luck!

Step #6: Automation - Separating the Men from the Boys

2008-12-28T21:21:00.021+02:00

Before we start, to make sure, when I'm talking about automation I'm referring to all sorts of ways to write a program in Stata that will run, and save output from, commands in a more-or-less structured program without having to write all commands separately. In the Stata manual you will sometimes see the term Automation as reserved to OLE automation, which is making Stata available to Microsoft Office programs. I will not deal with this automation at all.

Intro: Why?

OK, so what is automation good for? We had a preview in the previous step: One thing we can do is to calculate things based on outputs from commands we ran, before seeing the output and automatically by the program: divide mean by standard deviation, add 1.96 standard errors to the mean, and so on. Of course, one way (the boys way) is to run the command, and then calculate it with the di command, or in a spreadsheet or calculator. But the men don't do things manually. They tell their programs what to do.
Another thing we can do is to avoid repeating similar commands. Instead of having 50 rows of the same reg command but with different regressors or different samples, we can write a loop that will do it in 5 rows. This is good not only to save rain forests when you print out your code, but it also puts a structure to your regressions and reduces the chances that when you need to change something in the command (report clustered standard errors for example), you'll need to do it just once and not 50 times (or 48 times and forget to change 2 of the regressions accidentally).
Finally, what we can also do is to construct tables of the results we want to report. But we'll deal with this possibility in the following step.
When is it better to leave automation out? Probably when you need just a few regression and you're not doing anything too time-consuming with the output. Think of automation as an investment you do in your program. It entails a fixed cost of thinking about the structure and implementing it, but above a certain threshold the benefits of having the program do most of the work for you. What I used to do in many cases when I was starting to automate do-files, was to first write the program simply and then when I saw that I'm starting to repeat almost the same code (Copying and pasting like there's no tomorrow) I started thinking of how to automate things.

Macros
A macro is a word (or a string) that whenever we write it in a Stata command, before running the command, Stata replaces this word by a string (a set of characters) that is set for this macro, and only after the replacement, it runs the command.
Enough with the definitions, let's see a simple example:
To define a macro x that contains the value 6, run the following line (without the . in the beginning):

. local x = 6

Now Stata has assigned a place in memory called x and put 6 in it. So whenever we want to tell Stata to use this x we saved, we use backquote (usually the key in the top-left corner of the main keyboard, left to "1") and quote (single quote) characters:

. di `x' + 5
11

What really happens, behind the scene, is that Stata first sees the ` followed by the ', and looks within them to find a local we previously defined. Then it replaces this referral by the value that was saved:
First (as typed): di `x' + 5
Then (replacing `x' by 6): di 6 + 5
And only then when no `' are left in the command, Stata will run it and return 11.

Note: if nothing was defined for y, then Stata will replace the `y' by nothing:
First (as typed): di `y' + 5
Then (replacing `y' by an empty string): di + 5
And then you will get an error because the di command can't handle "+ 5" as input. Note that you will not always get an error. If Stata has no problem running the command after replacing the macro by an empty string, then it will run. Many annoying bugs in your programs will stem from this problem.

We can also put strings inside macros:

. local x = "Hi there, how are you?"

. local dependent_variable = "wage"

Why do we need the double quotes? Because otherwise Stata will think what we put after the = sign is part of our command and not simply a value. Specifically, when we put words (instead of numeric values) after the = sign, Stata thinks we're referring to a variable in the dataset (if there is, indeed a variable in that name, it will put the value of the first observation in this variable). Thus, to tell Stata you don't want the value of what's inside wage, but rather you want simply the name "wage" to be kept inside the macro dependent_variable

Now, let's see how we can refer to these macros. Almost the same as when it was numeric:

. di "`x'"
Hi there, how are you?

. sum `dependent_variable'

Why did we use the double quotes for the first command but not for the second?
Remember what the macro does, it replaces the `macro' with what we've saved in it. So for the first line this would be:
First (as typed): di "`x'"
Then (replacing for the macro): di "Hi there, how are you?"
So if we had not put the double quotes, it would had been equivalent to running:
. di Hi there, how are you?
But Stata will then look for a variable named Hi and won't find it. We didn't intend Stata to look up a variable, but to simply display a string as it is.

However, when we ran the second line with the summarize (sum) command, we indeed wanted the command to treat wage not as just a word, but as a reference to the variable wage! In other words, we wanted to run:

. sum wage

(and not sum "wage")

Three final remarks before we move on to loops (if you're still wondering why do we need all this, hang on).

local and global - you might have wondered why the command to define a macro is called local. In step #8, which hopefully will be written some day, we will deal with writing commands in Stata, and then it will matter. local means that the macro is defined within the program it was set in, and global means that all commands and programs can refer to the macro.

In any case, to define global, you will use the word global instead of local:

. global x = 6

But to refer to the global later, we do something a little different. We use ${macroname}

. di ${x}

Long strings - at least in Stata 9, there is a weird issue with defining macros for strings that are longer than 255 characters. You might think why do you need more than 255 characters, but it happens sometimes and unless somebody told you, this can be one of the most annoying bugs (Stata might simply cut your string after 255 characters...). To avoid that, what you need to do is define string macros without the = operator:

. local mylongstring "This is my very long string, and since it is longer than 255 characters, I omitted the = in its definition. Looks strange, but this is how it works. Good luck!"

Predefined macros - Stata has some macros within it that you might find helpful. They're not exactly macros, when I come to think of them, but we treat them as such (but without the `'). For example, _N holds the number of observations in the dataset (try to run di _N), _pi holds the number pi. For a few more, you can look up help _variables

Nested macro reference - You can refer to a macro within another macro reference. What does that mean? Say you have one macro named x_a and another macro named x_b, you can define a macro named i and do the following:

local x_a = 800
local x_b = 43.2
local i = "b"
di `x_`i''

Note that it is not double-quote at the end of the di command but two single-quotes. What happens when Stata hits the di command is the following:
First (as typed): di `x_`i''
Then (replacing the innermost macro): di `x_b'
Then (replacing the next innermost macro): di 43.2
And then Stata will execute the command and shoe the number 43.2

This can be handy when you have several macros and you want to alternate referring to them (i decides which of the x's to use), which sometimes you need to do inside loops.

Extended functions - Ever wondered how to save a variable's label? Ever wanted to count how many words there are in a string? Maybe you didn't, but sometimes there are things you need to save to a macro and you have no idea how to do that. In some of these cases, you might find your answer in the extended functions. They work a bit differently (but just like egen, you get many different features with variations on the same command):

local <macro_name> : <extended_function>

Note that we use : instead of = which tells Stata we're not using the regular functions but the extended functions. For example, say you want to keep the label of the variable w2gef (usually questionnaire data will have cryptic variable names but hopefully informative labels) inside the macro w2gef_label:

local w2gef_label : variable label w2gef

Another example:

local x "This is my string. How many words are in it?"
local num_words : word count `x'
local sixth_word : word 6 of `x'
di "There are `num_words' words in x. The sixth of them is `sixth_word'"

The output will then be:

There are 10 words in x. The sixth of them is many

Step #5: Using commands' outputs

2008-02-13T15:47:00.015+02:00

Now that we're done with data handling, we're skipping the part about statistical analysis commands (regressions and the like). The next four steps will deal with automation of the analyses. This step is actually a preface to the next three steps which deal with automation. As you know, Stata can run each command separately in the command window. It is also possible to save all the commands in a ".do" file and rerun all of them whenever you like. Since programs tend to run the same commands again and again, we would like to automate some of the commands. It will also help us organize our analysis and report the results in a neat table instead of with the regular text output of Stata. To do that, we will go through steps 5-8.

Don't worry if you don't understand why we're doing what we're doing in this chapter. That will be clearer in the following chapter.

The most basic statistical command in Stata is probably summarize. I'm relying on the fact that the reader is familiar with this command. If this is not the case, I think a more basic tutorial will fit you before going through this one.

So, let's say that I run this command (su is an abbreviation for summarize)

Now, what can you do if you want to use one of the statistics, for example, to calculate something with it? Suppose we want to calculate how much is 2 standard deviations. We see how much is one standard deviation (.409255) so we can manually calculate 2 * .409255:

But what happens if we're in a program, or if we want to use a more precise measure of standard error (Stata saves more than 6 figures after the dot)? In a program, we could not write "di 2*.409255". We can, but it will be bad coding. We would have to first run the program, get the standard deviation and then write it in the code. Moreover, if we just slightly change the data in the future, we will need to fix the code itself.

The solution is rather simple. Many commands keep their calculations for later use as scalars, vectors or macros. To see which calculations the summarize command saved, lets run "return list":

See, the command "return list" lists the scalars that the summarize command saves. Summarize, specifically, saves the calculations for the last variable specified (in our case it was just one). Now, we can run in our code, if we want to calculated generally 2 standard deviation of the variable we can simply run two commands:

This way, we didn't use the actual s.d in our command, but rather the r(sd) word. Stata replaced r(sd) with the value saved by the summarize command.

This trick can be done with other commands. Actually with all programs that are in the rclass category. I don't know where the rclass word came from, but I do know that it means that the scalars will be saved in r(something) command.

Another class of commands is the eclass commands. The command regress is one of them. If we will run reg, we can then look for saved values with "ereturn list":

We got the scalars (numbers) that are related to the regression (root MSE, number of observations, F-statistic, R-squared etc.) but also macros (which I personally never use) and most importantly: matrices. One is e(b) which is actually a vector of the coefficients (a matrix of 1 row and 2 columns) , and the other is e(V) which is the coefficients covariance matrix. Let's take a look at them:

And we see, indeed, the coefficients from the regression in e(b). Compare the output of the matrix list e(b) command and the output from the reg command. The e(V) matrix is a bit less self-evident. Remember that the covariance between a variable and itself is its variance. So the coefficient's standard error is actually the square-root of the value it has in the diagonal.

There is, however, a shorter way to reach the coefficient and the standard error. Instead of using the matrices, you can simply refer to the coefficient's value with the _b[] ... thing (I don't know how to call it). In order to get the value of the coefficient of x, put x between the brackets: _b[x] has the coefficient of x from the last regression. So now you can have your program calculate the "effect" of having 3 more rooms and not just one (or just save that number in order to report it in a table later). Also, in order to get the standard error, use _b[]'s brother, which is _se[]. That is:

As long as you didn't run another eclass command, the calculations from the last program will be available for you to use. Once you run the next eclass command (for example, another regression), the saved output will be replaced by the new command's output. The same goes for the rclass commands, but separately. That is, if you run an rclass command, it will not "step on" the eclass command's results, but only the last rclass command's results.

In order to put the results aside for later use (before running the next eclass or rclass command), we will learn how to use macros - in the next chapter.

(Go on to Step #6)

Step #4: Thank God for the egen Command

2007-12-19T13:30:00.002+02:00

Introduction

What's so special, really, about the egen (extensions to genereate) command? The answer is that it lets you do lots of things to the data. Things that in other statistical programs might take a lot of commands are possible to do with a couple of egen commands. So this is actually the next phase of data manipulation.

The syntax is pretty simple:

egen <new variable>= <function>(<expression(s)> or <variable(s)>) [, by (<variables>)]

The functions actually determine what the egen command will do. There are many of them, all described in help egen, and the following sectios of this step will describe the use of the most commonly used functions. These examples will hopefully clarify how to use the different functions and how can they help us.

mean()

egen store_mean_price = mean(price), by(store_id)

This example will create a variable in which, for each observation, the value will be the mean price of all observations that have the same store_id. See the figure under rowmean() for a graphic illustration.

One can omit the by option - this will put the mean of the original variable for all observations in the dataset.

Other examples:
egen mean_firm_occupation_wage = mean(wage), by(firm_id occupation_id)

This will put, for each observation, the mean wage of all other observatios with the same firm_id and occupation_id.

rowmean()

The function rowmean also compute means, but instead of computing means of a variable across observations, it compute the mean across variables for each of the observations.

egen mean_score = rowmean(math_score physics_score chemistry_score)

Suppose you had a dataset of students and their scores. This example will simply create a new variable - mean_score - which will hold the mean of math, physics and chemistry score for each of the students.

Note that the fact that it computes it separately for each of the observations makes the by option irrelevant. Take the previous example: There's no meaning to do add a by(class_id) option to the egen command when using the rowmeanfunction. If you want the mean score in class for any of the subjects (mean score across students), you should use the mean() function instead of the rowmean(). If you want the mean score (across students) of the mean score (across subjects), you need to first do mean and then rowmean (or vice versa).

You might ask, what's the difference between the rowmean() and simply using the gen command:

gen mean_score = (math_score + physics_score + chemistry_score) / 3

There are two main differences:
1. You can use wildcards - The same rowmean command can be written like this:

egen mean_score = rowmean(*_score)

This is very useful if the list of valiables is very long, or if you think that later on you might add english_score history_score, and so on, to your dataset, and you don't want to update this command every time. To learn more about wildcards, see help varlist.

2. Missing values - If one of the variables mentioned above is missing, gen command will not be able to sum the three variables and will therefore put missing value for mean_score in that observation. This is the case even if the other two scores are not missing. egen rowmean, on the other hand, will disregard the missing values and compute the mean of only the nonmissing values in the variable list. Only if there are missing values in all of the variables specified, egen rowmean will put missing value in the generated variable (just like the gen command). It's up to you to decide which one is better. Sometimes it is enough to have one missing value in order for the mean value to be irrelevant to what you are measuring, and sometimes you may decide that the mean of the nonmissing variables only is good enough.

This slide shows how the mean() and rowmean() functions work:

Note: Although the observations are sorted according to the by variable (class_id) here, it doesn't mean that you should sort them before. egen doesn't need the dataset to be sorted according to the by variable (although I'm guessing that if it's sorted, it will take less time to process).

sum() and rowtotal(), max() and rowmax(), min() and rowmin()

This is the same as mean and rowmean, but instead of calculating means, these functions calculate sums. Here are some examples:

egen team_effort = sum(effort), by(team)
egen total_correct_answers = rowtotal(question_*)

There is a small difference between rowmean and rowtotal in the way missing values are treated. rowtotal simply treats missing values as zeroes. So even if the values are missing in all the specified variables, the new rowtotal variable will be 0. rowmean would have put missing value instead. If you want to put a missing value there, you might want to do something like that:

egen total_correct_answers = rowtotal(question_*)
egen nonmissing_answers = rownonmiss(question_*) // This function puts the number of variables for which this observation had a missing value
replace total_correct_answers = . if nonmissing_answers == 0
drop nonmissing_answers

For max and min, the function gives the maximum or minimum value, respectively, out of all the values of the specified variable (within a group, if you are specifying the by option). Let's say, for example, that you want to normalize all values of the variable wage in a group so that each observation will have 1 if its wage is the maximum wage in a group, 0 if its wage is the minimum and some number between 0 and 1 if it's in between. The following set of commands will do the job pretty easily:

egen max_wage_f = max(wage), by(firm)
egen min_wage_f = min(wage), by(firm)

gen norm_wage_f = (wage - min_wage_f) / (max_wage_f - min_wage_f)
replace norm_wage_f = 0.5 if norm_wage_f == . // This is for the firms in which the maximum wage equals the minimum wage. The command above will give them missing values, because the denominator equals zero

Here is another example of using egen max in order to populate a nonmissing value to observations with missing values. Suppose you have a dataset of students in schools. In one of your regressions you want to take into account the number of children whose father dropped out of high school. In order to do so, you will the following commands:

egen f_dropout_kids_only = count(student_id) if f_educ<12, by(school) // This will count the number of children for whom the condition applies. Observations in which the condition does not apply, will get a missing value to the generated variable.

egen f_dropout_kids = max(f_dropout_kids_only), by(school)

drop f_dropout_kids_only // Don't get confused. This will drop the variable f_dropout_kids_only that is no longer needed

Although, in general, missing value is greater than any other nonmissing value, the egen max ignores missing values and therefore the observations with the nonmissing value in that school - i.e the kids whose father dropped out - will be given to the other observations in the same school (we need all of them in the regression: both the ones who apply and the ones who doesn't). The following figure might make it clearer.

Note: Instead of egen max, we could have also used egen min or egen mean again as the second command. Both egen min and egen mean ignore missing values and since the nonmissing values are equal for that by category, the functions will yield that same value. This is not true for egen sum, though, because the sum function will multiply the nonmissing value by the number of nonmissing observations.

For additional statistical calculations of the within group, see help egen and look for functions such as sd() (for standard deviation), median(), mode() and others. You can also calculate statistics across a group of variables on an observation-basis (instead of across a group of observations on a variable-basis). See help egen and look for the functions that start with row : rowmean(), rowmin(), rowmax(), rowsd(), rowtotal(), etc.

tag() and group()

These two function are really useful with identifying variables that have more than one observation (group-identifying variables) - the same ones we used before in the by options.

Suppose you have a dataset of gas prices. Each observation has the type of fuel, the price per gallon, the station ID, and week in which the price was recorded. Not all stations were recorded in each of the weeks. That is, in some weeks, some stations didn't have their price taken. In your research, you decide to work only on stations for which you have full data - i.e, those which appear in each of the 50 weeks.

If the data had only one observation per station-and-week combination, you could have just used the count() function of egen:

egen station_count = count(week), by(station) // This will count the number of observations with non-missing values in week, for each value of station, and put the result for each observation of that station.

The problem is that each station-week combination has more than one observation and the number of observations per station-week varies between stations (remember, each station-week has as many observations as the number of fuel types - a price for leaded, unleaded, premium, etc.). Simply counting observations will not work here. We need to "tag" stations: in other words, we will create an indicator (a dummy variable) which will be 1 for only one observation per station, and 0 for all other observations of the same station.

Once we tag each new combination of station-week, we can count how many station-week combinations there are for each station -- this will give us the number of weeks for each station. Although I said we need to count, we will use the sum() function of egen, because count() will add to the count observations with 0, whereas sum will not (which is good because we do not want to count the same station-week more than once):

egen station_week_tag = tag(station week) // We're not using the by option since the group-identifying variables are already in the tag.

egen weeks_of_station = sum(station_week_tag), by(station)

Graphically, this is what we actually do:

The group() function is used in the same manner we use the tag() function, but instead of putting 1 in each new combination and 0 in combinations that it has already seen, it puts 1 for all the observations of the first combination it sees, then 2 for all the observations of the second combination, and so on. The benefit of this function is that you can create a full numeric ordered single variable that enumerates all combinations. When we will deal with loops, it might be clearer why this is good.

Conclusion

The egen command can help you play with the data pretty easily and intuitively (once you get the trick of the function you are using). There are other functions of egen I did not describe here. As I said, you can use the help egen, and don't be afraid to experiment with the functions.

To check whether your function work, browse your dataset. Sort it first by the group variables you mentioned, and then just browse the variables you want to. If you have a large dataset, you can limit the browse command using if conditions or in. Here are two examples:

sort firm

browse firm employee wage min_wage_f max_wage_f norm_wage_f in 2000/2200 // This will browse observations #2000 through #2200

browse firm employee wage *_wage_f if firm >= 100 & firm <= 200

You can do the same with the list command, by the way (but list is limited to the width of the output screen).

Good luck!

(Go on to Step #5)

Step #1 - Getting the data

2007-12-06T11:46:00.000+02:00

The first encounter with Stata, in many cases, can be one of the most frustrating ones. You got the first project you need to work on your own, you searched the internet for data, or got it from someone, but it's not in Stata format. Transferring the data can be very annoying if you don't know how to do it. Maybe this example can help.

Suppose you need to construct a dataset for a research on OECD countries' Foreign Aid. You log into http://www.oecd.org/, go to Statistics, and get the data you want. Then you want to export the data from the website and it lets you do that to an Excel file. I got the following file:

There are, as you can see, four variables: Country, Year, ODA, and ODA as % of Gross National Income. A crucial point is to get the data right in the website, but I am ignoring that here. As a general advice, always ask yourself if the numbers make sense. If you won't ask yourself, others will ask you!

In any case, there are a few things we better do before we transfer the data. Keep in mind that we want to get to the point where the table has one header row with the variables names, and the rest of the rows represent observations:

The country column have only one cell containing each country's name. The rest of the cells should be filled with the name too. Otherwise, this row - when it will be transformed to an observation in Stata - will have a missing value instead of the country's name. To solve this, select the cell with the country's name, click on the bottom-right corner of the selected rectangle and drag it down all the way to the last empty cell of this country. In my file, I also needed to unmerge the cells. Repeat this for all the countries.

The cells in the year column have this weird green triangle on their top left corner. This means that the values there are stored as Text instead of as numbers. Excel recognized this pitfall and therefore warns us with the green triangle. We must reformat the values from text to numeric. Otherwise, Stata will not be able to perform any calculation on this variable. To convert the cells formats to numbers, first select the first cell, then drag the selection all the way down. Click on the small icon with the exclamation mark and click on "Convert to Number"

Column C and Rows 1-6 are excessive. Copy, or type, the column headers for columns D and E in row 7, and delete rows 1-6 and column C.

This is optional, but I would suggest to delete the DAC Countries, Total rows too, since they are not really observations which represent countries, and we want our dataset be country-year based.

Now, you got an Excel file that looks like the Stata file you want. Particularly, all numeric variables have values that are classified as numbers. To transfer them into Stata, you have three options:

The safest, but most expensive - Use a data-transfer program like StatTransfer.
The cheapest way - Copy & Paste.
The third way - Export from Excel, then import to Stata.

Data-transfer Software

These kinds of programs will enable you to transfer data from, and to, different formats, easily and reliably. The problem is that they cost money, so if you're only into a small project, they probably don't pay off. If you are working on a university computer, maybe you have this software installed somewhere. For more details about StatTransfer, visit http://stattransfer.com/

Copy & Paste

This will work in most cases, but might be problematic sometimes. For example, if you have a column with percentage format. Go to the worksheet in Excel, select all the columns that you need (click on the first column's letter and drag to the last column). Copy the selection (Ctrl+C or right-click and then copy or any other way you can think of). Now launch Stata. In the command window type edit and press enter. A window with an empty matrix should appear. Now paste (Ctrl+V etc). Voila!

Export & Import

Go to the worksheet in Excel, select from the menu bar File->Save As. In the bottom of the window that opened you have "Save as type". Select either "Text (Tab delimited) (*.txt)" or "CSV (comma delimited) (*.csv)". Choose a name for the file and remember the folder you saved it in. You will need this file's full path (e.g "C:\Documents and Settings\bla\My Documents\OECD Aid.csv"). Now press on the Save button. The program might ask you whether you are sure you want to save in this format, select Yes.

Launch Stata. In the command line, write,

If you selected CSV as the "Save as type":

insheet using "C:\Documents and Settings\bla\My Documents\OECD Aid.csv", comma

If you selected Text as the "Save as type":

insheet using "C:\Documents and Settings\bla\My Documents\OECD Aid.txt", tab

You can use the Import & Export method with other Spreadsheet programs. As long as they can export the data in a tab- or comma-delimited format.

Before you celebrate

Now, make sure you got the variables right. A simple way is to type sum in the command window. Stata will report, for each variable, the number of observations, mean, standard error, minimum and maximum values. Look at these numbers and make sure they make sense. Make sure that all numberic variables have the number of observations in the dataset. Missing values or string values are not counted by the sum command, so it's a good way to see if you got too many of them.

Another way to make sure the data you have is right is the tabulate command, or in short: tab. Running this command with a variable: tab donor will print a histogram of the variable donor. This way, we can make sure each country appears 7 times. When I run tab, I usually run it with the missing option (or just m, for short). If I don't ask for that option, Stata will not count observations for which the value of the variable (country in our case) is missing.

As you can see, the countries' observations are fine, but look at the value at the bottom of the tabulation. This is the result of the last row in the Excel file we used. It was a note below the table that Stata mistakenly took as another observation. We should get rid of it. The easiest way to do it is to type edit in the command line, scroll all the way down, select the observation and click on the "Delete..." button at the top of the Data Editor, and ask it to delete this observation. Then, when you close the window, a popup comes up and asks you whether to accept the changes. Accept them, of course.

Run the tab command again to see you didn't ruin anything by accident.

If everything is all right, save the file. Either use the icon in the top bar, or just type:

save "C:\myfilename"

If there is already a file with that name in that folder, Stata will not let you save like this. You will need to type save "C:\myfilename", replace.

Congratulations!

(go on to Step #2)

Step #3 - Simple Data Manipulation

2007-12-05T18:28:00.000+02:00

OK, so you have all your data gathered from all sources, and now you need to define additional variables, or drop some data that is irrelevant for your sample. To do so, you will first need to think about the statistical analyses you will want to do later on. If your Econometric model is quadratic, for instance, or in other words, if you measure the effect x on y with both x and x-squared, you need to create a variable with x-squared. Also, it is very common to see log-level and log-log models, in which the natural log of some variables are run instead of the variables themselves. From the project's point of view, the data handling from now on will take place in parallel with the statistical analysis.

Variables which are simple functions of other variables
To create a variable, you should use the gen command. Its basic syntax is very simple:

gen <new variable> = <expression>

For example:
gen income_squared = income ^ 2
gen ln_income = log(income)
gen gdp_per_capita = gdp / population

Stata will generate a new variable, and for each of the observations, it will evaluate the expression specified according to the values in the variables we use in the expression. In the first example, for an observation with an income of 200, income_squared will contain 40,000, but for an observation with an income of 100, income_squared will contain 10,000.

You may use an expression that is independent of any variable. This way, the variable will be constant over all observations.

Variables which are determined by a condition
Now, suppose you want to create a variable like this:

(1)
educ_parent = father's education if the student is male and mother's education if the student is female.

(2)
education_level = 0 if education equals 0
education_level = 1 if education is higher than 0 and lower-or-equal than 6
education_level = 2 if education is higher than 6 and lower-or-equal than 9
education_level = 3 if education is higher than 9 and lower-or-equal than 12
education_level = 4 if education is higher than 12

In order to do this, you will need to use both the gen and the replace command. Let's first look at the replace command.

replace <existing variable> = <expression>

The only difference between replace and gen is that gen creates a new variable and puts values in it, while replace takes an already existing variable and puts values in it. Actually, if you will type help gen and help replace, you'll get to the same manual page.

Let's look at the first example again. We need to first generate a variable named educ_parent that will be equal to the father's education, for boys:

gen parent_educ = father_educ if boy == 1

Notice the condition we added. In many Stata commands, conditions can be specified so that the command will be run only on observations for which the condition holds. Notice the double equality sign in the condition as opposed to the single equality sign in the beginning of the statement. Put simply, the single sign determines a value while the double sign checks whether the values are equal. In other words, it's like the difference between saying "set x to be equal to y" (x=y) and saying "are x and y equal?" (x==y).
In the gen case, observations for which the condition does not hold, missing values will be assigned. In the replace case, these observations will simply stay with their previous values.
Therefore, going back to our example, all that is left now is to run the following command:

replace parent_educ = mother_educ if boy != 1

More on Conditions
Before we move on to the next example, here are two comments about conditions:

You can combine several comments with logical operators (and, or, not). :

if x==y x==z // This means "if x equals y OR x equals z"
if boy==1 & immigrant == 1 // This means "if boy equals 1 AND immigrant equals 1"
if !(income>100000) // This means "if income is not greater than 100,000
if income<=100000 // This is equivalent to the previous line.
if income<=100000 & (profession == "Banker" profession == "Doctor") // This selects observations of people who are either bankers or doctors with income greater than 100,000. Note the parenthesis around the OR combination. If they weren't there, the condition would selected bankers with income greater than 100,000 and all doctors (regardless of income).

Very Very Important - Missing values are a dangerous pitfall! Look at the following examples:

if income > 100000 // In Stata, observations with missing values will also fulfill the condition
if boy != 1 // If you meant girls, they will fulfill the condition, but also observations in which boy has a missing value

In order to not to capture the observations with the missing values with the ones you are really looking for, try the following conditions respectively:

if income > 100000 & income != .
if boy != 1 & boy != .
if boy == 0 // This will do the same job, but it's shorter and more readable.

Back to the second example above. To create the variable education level, we will use a gen command followed by a sequence of replace commands. I will write two possible implementions that creates exactly the same variable:

// One possibility
gen education_level = 0 if education == 0
replace education_level = 1 if education > 0 & education <= 6
replace education_level = 2 if education > 6 & education <= 9
replace education_level = 3 if education > 9 & education <= 12
replace education_level = 4 if education > 12 & education != .

// Another possibility:
gen education_level = 0
replace education_level = 1 if education > 0
replace education_level = 2 if education > 6
replace education_level = 3 if education > 9
replace education_level = 4 if education > 12
replace education_level = . if education == .

TIP:

After defining a new variable, especially if it is not a simple one, it is always a good thing to tabulate it against the variable you are constructing it on:

Creating Dummy Variables with a Condition
Dummy variables, or indicator variables, are variables which have either the value of 0 or 1 (if they are not missing). For example, the variable is_working can have either 0 if not working or 1 if working. The creation of a dummy variable is made with a condition (but without if preceding it):

gen <dummy variable> = <condition>

Examples:

gen is_working = income_from_labor > 0
gen high_demand = demand > 600

Problem - Missing Values
Take a look at the second example. What if some observations have missing values in demand. In the example given, they will get 1 in high_demand. So you will probably say: "Hey, you already talked about it. I'll run it like this:"

gen high_demand = demand > 600 & demand != .

But this will make the value of high_demand to be 0 for observations with missing demand. Is this something you want to do? Well, sometimes. But in other times, you don't want to classify missing demand as "not high", but to keep high_demand missing too. In order to do that, you need to run the following command instead of the one above (note the difference):

gen high_demand = demand > 600 if demand != .

Now, why is that? Good question.
A condition - whether defining the dummy variable or specified after the if word - is actually a function that returns either 1 - if the condition is met - or 0 - if the condition is unmet. For each observations the value might be different. Once you specify a condition after an if word, Stata understands that the command is executed only on observations which meet the condition (for which the condition returns 1). But when you specify it after the = (remember the meaning of a single equality sign?), it tells Stata to put the value that the condition returns, 1 or 0, in the variable generated.

Getting Rid of Excess Data
You might want to drop some observations or variables that you don't need. That may improve running time, so you should consider whether you really need all the variables you have. You also want to reduce the complexity of the dataset and keep it as thin as possible.
Two opposite commands will do the job: drop and keep.

For both commands, you need to think first if you want to drop variables or observations. If you want to drop variables, the syntax is:

drop <variable(s) name(s)>
keep <variable(s) name(s)>

Examples:
drop gdp_per_capita_ppp gdp_per_capita_current // This will drop the variables specified and keep the rest
keep id school_id treatment average_score // This will keep the variables specified and drop the rest

If you want to keep or drop observations, the syntax is a bit different:

drop if <condition>
keep if <condition>

Examples
drop if sales_mil < 100 // This will drop all observations (companies for example) with sales less than $100M, and keep the rest of observations.
keep if problematic_education == 0 // This will keep observations with problematic_education == 0 and drop the rest (the problematic ones, as defined previously in the program)

Notice that the if here acts the same way as in the gen and replace commands: it tells Stata which observations are relevant to the command.

(Go on to Step #4)

Step #2 - Combine Multiple Datasets into One

2007-12-03T13:27:00.001+02:00

In many cases, the data needed for the statistical analyses come from different sources. For example, if you want to analyze international growth, you might find economic indicators in a dataset of the World Bank, political indicators in think tanks such as Freedom House, and climate data in another dataset. Another case is when you have one dataset which is divided into multiple files. In this post I will try to elaborate a bit on how to make it work.

Types of Datasets Combinations

There are actually two main types of combinations:

"Vertical" combination - You want to do this when you want to add observations from one file to another file. For instance, if you are working on a sports statistics project and you have data for players performance in four separate files, one for each year between 2001 and 2004. Another possibility is that the data is separated according to different leagues, groups, etc. As long as the variables in the files are the same and the only thing you need to do is to add observations, this is vertical combination. The command in Stata we will use is append. We will explore this command later.

"Horizontal" combination - This is the kind of combinations in which you want to add variables, and not observations. The observations appear in both files (at least most of them), but in each file there is different information about them. For example, if we're dealing with high school students and we have one file with their personal information and grades, and another file with SAT scores only. If we have an identifying variable in both files (e.g Social Security Number), we can assign each student his/her SAT score. This example is a One-to-One matching. There are three types of matches of this kind:

One-to-One matching: If the identifying variable which appears in the files is unique in both files, then it's a one-to-one match. Unique means that for each value of this variable, there is only one observation that contains it. In the figure below, country is the identifying variable. In both datasets, each country has only one observation.

One-to-Many matching: If the identifying variable is unique in one file, but not unique in the other, then it's a one-to-many matching. This is very common when you have groups of observations in one file (the file with the identifying variable which is not unique), and information regarding each group in another file (the other file). The following figure will make it clearer:

As you can see, one can group the individuals to housefolds. The household identifying variable (fam_ID) is common to both of the files. It is not unique in the individuals file, but it is unique in the households file. This enables Stata to assign the same value, of each of the households variables, to all the members of the household. Note that although we have a unique identifier for the individuals (indiv_ID), it is irrelevant for this merge of files.

Many-to-Many matching: This is very rare. This is also problematic, since there is no unambiguous rule for the assignment of values from observations in one file to observations in the other file. I will not elaborate on this matching too much.

Commands Syntax

There are three commands you should know if you want to combine datasets: append, merge and joinby. All three of them combine the dataset currently in memory with data from a file you specify. We will name the data in memory "Master Data" and the data to combine from the specified file "Using Data". It will be clear why we use the word Using here.

Append

The append command does what we called "vertical" combination. It adds observations. It's syntax, in a simple form (for options not specified in this tutorial, you can always type help append in the command line in order to explore more about the command), goes like this:

append using <filename>

Example:

append using "C:\more_observations.dta"

append using "C:\more_observations" // (this is equivalent)

This will add the observations from the file C:\more_observations.dta to the data in memory. In case no extension is specified (i.e no .dta at the end of the filename), Stata assumes it's .dta, so you can omit it.

Now you understand why we call the data in C:\more_observations.dta "Using Data".

What happens if you have variables in the Master Data which do not exist in the Using Data? The observations from the Using Data will be assigned missing values in those variables. If there are additional variables in the Using Data which do not appear in the Master Data, the observations from the Master Data will have missing values in them.

Tip: Before you append, you might want to make sure you know the source file for each observations. For example, if you append 2008 data to 2007 data currently in memory, you might want to make sure you have the variable year in each of the datasets prior to the incorporation of the Using Data.

Merge

For "horizontal" combination of datasets you will need either merge or joinby. The difference between them is the method they use in order to do the merging, but in one-to-one or one-to-many merges, they give almost the same functionality. We will start with the merge command. The syntax, in its simplest form, is:

merge <identifying variable(s)> using <filename>

Examples:

(1)
use "D:\geography", clear // Assumes D:\geography.dta"
merge country using "D:\economy" // Assumes "D:\economy.dta"

(2)
merge fam_id using "K:\households.dta"

(3)
merge state year using "K:\USA_data\precipitation.dta"

In the first example, Stata first loads observations from a file called geography and then matches them to observations in the economy.dta file. This will do what the figure in the one-to-one section above shows.
Note: what comes after the double forward-slash (//) will be ignored by Stata. It's used to make the code clearer to the human reader

In the second example, assume the individuals dataset is already in memory. I tried to do what the figure in the one-to-many section above shows. Notice that there is no difference in the syntax of the command. The only difference is in the structure of the files you are operating on.

In the third example, I wanted to show you can use more than one identifying variable. In case only combination of variables is unique (and you want to identify observations uniquely), you can specify both of them. In this example, suppose you have data on state-year basis (this is called Panel Data, because you have the same subjects reappearing in different instances) - let's say car accidents data (number of accidents, injuries, etc) and you need to add data about the weather conditions in that year, you need to tell Stata to make the match between the datasets according to both state and year.

Important: The merge command requires that both the Master and Using Data will be sorted by the identifying variables. If the Master Data isn't sorted, run sort <identifying variable(s)>before the merge command. If the Using Data isn't sorted, open it first (use <filename>, clear), then run the sort command, then save it (save <filename>, replace), open the Master Data and run the merge command. Here's an example:

use "D:\economy", clear
sort country
save "D:\economy", replace
use "D:\geography", clear
sort country
merge country using "D:\economy"

1) Since you saved D:\economy.dta in the third line, you will not need to open D:\economy.dta and sort it again in future runs.
2) If you are doing a one-to-one match (i.e if the identifying variable(s) are unique in both sets), you can run the merge command with the sort option. It will automatically sort the datasets within the merge command. The sort option will not work if the identifying variables are not unique.

The _merge variable:

The merge command automatically creates a variable named _merge, which contains information regarding the observation's existence in each of the two datasets. In the simple cases I mentioned above, it will contain, for each of the observations, one of the following values:
1 => the observation (the identifying variable(s) values) appeared only in the Master Data
2 => the observation (the identifying variable(s) values) appeared only in the Using Data
3 => the observation (the identifying variable(s) values) appeared in both datasets

It is up to you to decide what you want to do with each of the cases. In some projects you will not want observatios with the value 2 in the _merge variable. For example, take example 2 above. If you have households data in the Using data, but your interest is individuals (in the Master Data), you don't need observations with household data but without individuals that are linked to it. If you want to get rid of it, you can either type drop if _merge == 2 after the merge command, or, even better, run the merge command with the option nokeep. That is:
merge fam_id using "K:\households.dta", nokeep

You can also decide that observations in the Master Data that has no corresponding observations in the Using Data are irrelevant for your research. In that case, there is no special option for the merge command. So you need to add the command drop if _merge == 1 after the merge command.

Other options of interest

update and replace

What happens if you have some overlap between the variables in the files? Say, when you are merging data from the CIA World Factbook and the World Bank, you might have GNI in both datasets. If you specify none of them, Stata will keep the values that were in the Master Data (in memory). If you specify the options update replace (replace can't be specified without update), Stata will take, instead, the values that are in the Using Data and put them in place of the Master Data values. If you just type the update option (without replace), however, Stata will put the Using Data values only in observations where the Master Data values are missing.

So in case you have the same variable but different values, use neither option when you think the Master Data is more reliable. Use the update replace options if you think the Using Data is more reliable. If they are equally reliable, use just update.

If you specified the update option, _merge will contain 5 possible values:
1 => the observation (the identifying variable(s) values) appeared only in the Master Data
2 => the observation (the identifying variable(s) values) appeared only in the Using Data
3 => the observation (the identifying variable(s) values) appeared in both datasets and the values are the same in both
4 => the observation (the identifying variable(s) values) appeared in both datasets and the value in the Master Data is missing.
5 => the observation (the identifying variable(s) values) appeared in both datasets but the values in the datasets are not missing and not the same.

Examples:

merge country using "D:\Economy", update replace

merge id using "K:\second_version", update

keep

If you want only some variables to be merged, instead of all of them, you can specify keep().

Example:

merge country year using "F:\intl_health_stats.dta", keep(birth_rate death_rate)

unique, uniqmaster, uniqusing and sort

In order to make sure the one-to-one or one-to-many matches are really unambiguously defined, you can make sure the identifying variables are unique in either the Master Data (uniqmaster), Using Data (uniqusing) or both datasets (unique). It is really recommended to specify them, although it won't change the functionality. The main contribution of these options is to make Stata print an error and exit if what you think is unique is not really unique. The sort option can make the merge command sort the datasets on its own, but it is only possible if you're running a one-to-one match (in other words, sort implies unique).

More than one dataset

You can merge more than one file in one command. Instead of specifying one filename after using, you can add more filenames. Unless the nosummary option is specified, the command will create _merge1, _merge2, ... , _mergen variables in which the observation's value in each of the _mergek variables will be 1 if the k-th dataset had this observations and 0 otherwise. The _merge variable will still be there, but now the value 3 in it means that the observations appeared in at least one of the Using datasets.

Personally, I prefer running the merge command iteratively and adding one dataset at a time. It requires to drop the _merge variable each time, and it might take longer time, but I can better report and deal with the merging outcomes.

Joinby

The joinby command does almost the same job merge does, but its internal working is different, so there might be differences in terms of processing time. Its main difference arises when you're dealing with many-to-many matches, but it can be used for one-to-one and one-to-many matches too. The simple syntax is:

joinby <identifying variable(s)> using <filename>

Example:

joinby country using "D:\economy"

Unlike merge, the default of joinby is to drop all observations that do not appear in both datasets. In order to keep those observations, you need to use the unmatched() option. This option has four possible variations:

unmatched(none) - Keep none of the unmatched observations (this is the default)
unmatched(master) - Keep observations in Master Data that have no match in Using Data (but not vice versa)
unmatched(using) - Keep observations from Using Data that have no match in Master Data (but not vice versa)
unmatched(both) - Keep all unmatched observations, from both Using and Master Data

So if you want to do the same thing done in the first example of the merge command, use the following commang:

joinby country using "D:\economy", unmatched(both)

There is no need for the datasets to be sorted by the identifying variable(s), which is an advantage over merge.

The update and replace options are available for joinby too.

As I said, more details with:

help joinby

Many-to-Many Merge

Although I have never needed it, this is where merge and joinby will give you totally different results. The question is how to match values from one dataset to the other. I think the best way to explain the difference between the commands is graphically:

Now you can understand the meaning of the sentence describing the joinby command in the help reference: "Form all pairwise combinations within groups".

Conclusion

If you want to add observations: append.
If you want to add variables: merge or joinby

As always, before you celebrate, make sure you got the combination of the files right by looking at the means, counts, minimum and maximum values (sum command) and tabulations (tab command). Take a special look at the _merge variable. Look for missing values or other outlying observations. If you have too many of them, you might have made a mistake along the way. Browse the data a bit. See that the data merged correctly.

Don't forget to save the file (that is, if you don't want to rerun the merge command later).

(go on to Step #3)