clear *
use "fake_data.dta", clear
ECON 490: Conducting Within Group Analysis (7)
Prerequisites
- Be able to effectively use Stata do-files and generate log-files.
- Be able to change your directory so that Stata can find your files.
- Import datasets in csv and dta format.
- Save data files.
Learning Outcomes
- Create new variables using the command
egen
. - Know when to use the pre-command
by
and when to usebysort
. - Use the command
collapse
to create a new data set of summary statistics. - Change a panel data set to a cross-sectional data set using the command
reshape
.
7.1 Introduction to Working Within Groups
There are times when you need to consider workers as a group. Consider some of the following examples:
- You would like to know the average wages of workers by educational grouping, in each year of the data.
- You would like to know the standard deviation of men and women’s earnings, in each geographic region in the data.
- You would like to know the top quintile of wealth, by birth cohort.
This module will show you how to calculate these statistics using the fake data data set introduced in the previous lecture. Recall that this data set is simulating information of workers in the years 1982-2012 in a fake country where a training program was introduced in 2003 to boost their earnings.
Let’s begin by loading that data set into Stata:
7.2 Generating Variables using generate
When we are working on a particular project, it is important to know how to create variables that are computed for a group rather than an individual or an observation. For instance, we may have a data set that is divided by individual and by year. We might want the variables to show us the statistics of a particular individual throughout the years or the statistics of all individuals each year.
Stata provides functionality to easily compute such statistics. The key to this analysis is the pre-command by
, and the only requisite to using this is to ensure data is sorted the correct way.
Let’s take a look at our data by using the browse
command we learned in Module 5.
%browse 10
We can tell here that the data is sorted by the variable workerid.
We use the pre-command by
alongside the command generate
to develop these group compounded variables. If we use variables other than workerid (the variable by which the data is sorted) to group our new variable, we will not be able to generate the new variable.
When we run the command below Stata will produce this error.
drop var_one
cap by year: gen var_one = 1
If we want to group by year, Stata expects us to sort the data such that all observations corresponding to the same year are next to each other. We can use the sort
command as follows.
sort year
%browse 10
Let’s try the command above again, now with the sorted data.
drop var_one
cap by year: gen var_one = 1
Now that the data is sorted by year, the code works!
We could have also used the pre-command bysort
instead of by
. When we do this we can skip the command to sort the data. Everything is done in one step!
Let’s sort the data, so it is reverted back to the same ordering scheme as when we started, and generate our new variable again.
sort workerid year
drop var_one
cap bysort year: gen var_one = 1
The variable we have created is not interesting by any means. It simply takes the value of 1 everywhere. In fact, we haven’t done anything that we couldn’t have done with gen var_one=1
. We can see this by using the summary
command.
su var_one
You may not be aware, but Stata records the observation number as a hidden variable (a scalar) called *_n* and the total number of observations as *_N*.
Let’s take a look at these by creating new two variables: one that is the observation number and one that is the total number of observations.
drop obs_number
cap gen obs_number = _n
drop tot_obs
cap gen tot_obs = _N
%browse 10
As expected, the numbering of observations is sensitive to the way that the data is sorted! The cool thing is that whenever we use the pre-command by
, the scalars _n
and _N
record the observation number and total number of observations for each group separately.
drop obs_number
cap bysort workerid: gen obs_number = _n
drop tot_obs
cap bysort workerid: gen tot_obs = _N
%browse 10
As we can see, some workers are observed only 2 times in the data (they were only surveyed in two years), whereas other workers are observed 8 times (they were surveyed in 8 years). By knowing (and recording in a variable) the number of times a worker has been observed, we can do some analysis based on this information. For example, in some cases you might be interested in keeping only workers who are observed across all time periods. In this case, you could use the command:
keep if tot_obs==8
%browse 10
7.3 Generating Variables Using Extended Generate
The command egenerate
is used whenever we want to create variables which require access to some functions (e.g. mean, standard deviation, min). The basic syntax works as follows:
bysort groupvar: egen new_var = function() , options
Let’s see an example where we create a new variable called avg_earnings which is the mean of earnings for every worker. We will need to reload our data since we dropped many observations above when we used the keep
command.
clear *
use "fake_data.dta", clear
drop avg_earnings
cap bysort workerid: egen avg_earnings = mean(earnings)
drop total_earnings
cap bysort workerid: egen total_earnings = total(earnings)
By definition, these commands will create variables that use information across different observations. You can check the list of available functions by writing help egen
in the Stata command window.
In this documentation, you will notice that there are some functions that do not allow for by
. For example, suppose we want to create the total sum across different variables in the same row.
drop sum_of_vars
cap egen sum_of_vars = rowtotal(start_year region treated)
The variable we are creating for the example has no particular meaning, but what we need to notice is that the function rowtotal()
only sums the non-missing values in our variables. This means that if there is a missing value in any of the three variables, the sum only occurs between the two variables that do not have the missing value. We could also write this command as gen sum_of_vars = start_year + region + treated
; however, if there is a missing value (.
) in start_year, region or treated, then the generated value for sum_of_vars will also be a missing value. The answer lies in the missing observations. If we sum any number with a missing value (.
), then the sum will also be missing.
We can also use by
with a list of variables. Here will use year and region in one command.
drop regionyear_earnings
cap bysort year region : egen regionyear_earnings = total(earnings)
What this command gives us is a new variable that records total earnings in each region for every year.
7.4 Collapsing Data
We can also compute statistics at some group level with the collapse
command. Collapse is extremely useful whenever we want to apply sample weights to our data (we will learn more about this in Module 11). Sample weights cannot be applied using egen
but are often extremely important when using micro data. Those weights allow us to manipulate our data to better reflect the composition of the data when the authority that collected the data might have over sampled some segments of the population.
The syntax is
collapse (statistic1) new_name = existing_variable (statistic2) new_name2 = existing_variable2 ... [pweight = weight_variable], by(group)
You can obtain a list of possible statistics by running the command help collapse
. You can also learn more about using weights by typing help weight
.
Let’s suppose we want to create a data set at the region-year level using information in the current data set, but we want to use the sample weights that were provided with our data. First, we decide which statistics we want to keep from the original data set. For the sake of explanation, let’s suppose we want to keep average earnings, the variance of earnings, and the total employment. We write the following:
collapse (mean) avg_earnings = earnings (sd) sd_earnings = earnings (count) tot_emp = earnings, by(region year)
%browse 10
Warning: When you use collapse
, Stata produces a new data set with the results and in the process drops the data set that was loaded at the time the command was run. If you need to keep that data, be certain to save the file before you run this command.
7.5 Reshaping
We have collapsed our data and so we need to import the data again to gain access to the full data set.
clear *
use "fake_data.dta", clear
Notice that the nature of this particular data set is panel form; individuals have been followed over many years. Sometimes we are interested in working with a cross section (i.e. we have 1 observation per worker which includes all of the years). Is there a simple way to go back and forth between these two? Yes!
The command’s name is reshape
and has two main forms: wide
and long
. The former is related to a cross-sectional nature, whereas the latter relates to the usual panel nature.
Suppose we want to record the earnings of workers while keeping the information across years.
reshape wide earnings region age start_year sample_weight, i(workerid) j(year)
Warning: This command acts on all of the variables in your data set. If you don’t include them in the list, Stata will assume that they do not vary across i (in this case workers). If you don’t check this beforehand, you may get an error message.
%browse 10
There are so many missing values in the data! Should we worry? Not at all. As a matter of fact, we learned at the beginning of this module that many workers are not observed across all years. That’s what these missing values are representing.
Notice that the variable year which was part of the command line (the j(year)
part) has disappeared. We now have one observation per worker, with their information recorded across years in a cross-sectional way.
How do we go from a wide
data set to a regular panel form? We need to indicate the prefix in the variables, which are formally known as stubs
in the Stata lingo, and use the reshape long
command. When we write j(year)
it will create a new variable called year.
reshape long earnings region age start_year sample_weight, i(workerid) j(year)
%browse 10
Notice that we now have an observation for every worker in every year, although we know some workers are only observed in a subset of these. This is known as a balanced panel.
To retrieve the original data set, we get rid of such observations with missing values.
keep if !missing(earnings)
%browse 10
7.6 Wrap Up
In this module, you have developed some very useful skills that will help you explore data sets. Namely, these skills will help you both prepare your data for empirical analysis (i.e. turning cross sectional data into panel data) and create summary statistics that you can use to illustrate your results. In the next module, we will look at how to work with multiple data sets simultaneously and merge them into one.
7.6.1 Wrap Up Table
Command | Function |
---|---|
by |
It is a pre-command used to Repeat Stata command on subsets of the data |
generate |
It generates variables |
sort |
It sorts data |
summary |
It summarizes statistics of a dataset |
_n |
It records the observation number |
_N |
It records the total number of observations for each group separately |
drop |
It drops variables or observations |
keep |
It keeps variables or observations that satisfy a specified condition |
egenerate |
It create variables that require access to some functions |
rowtotal() |
It sums non-missing values for each observation of a list of variables |
collapse |
It makes a dataset of a summary of statistics |
reshape |
It converts data from wide to long and vice versa |
7.6.2 Errors
1. Sort
To develop group compounded variables, ensure that you first sort the observations by the variable. Not sorting the obserations will return an error code.
drop var
cap by sex: gen var = _n
The correct method of of generating compounded variables is below:
drop var
cap bysort sex: gen var = _n
var su
2. Reshape Error
Reshaping data can be tricky and doing so incorrectly can cause many variables to be dropped in the proccess. The command reshape error
can be used to identify the issues encountered when reshaping data.
clear *
use "fake_data.dta", clear
reshape wide earnings sex, i(year) j(workerid)
reshape error
7.7 Video tutorial
Click on the image below for a video tutorial on this module.
References
Reshape data from wide format to long format
(Non StataCorp) How to group data in STATA with SORT and BY