import stata_setup
'C:\Program Files\Stata18/','se') stata_setup.config(
07 - Conducting Within Group Analysis
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 data sets 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.0 Intro
>>> import sys
>>> sys.path.append('/Applications/Stata/utilities') # make sure this is the same as what you set up in Module 01, Section 1.3: Setting Up the STATA Path
>>> from pystata import config
>>> config.init('se')
7.1 Introduction to Working Within Groups
There are times when we will want to analyze our data while considering observations as a part of a group. Consider some of the following examples:
- We would like to know the average wages of workers by educational grouping, in each year of the data.
- We would like to know the standard deviation of men and women’s earnings, by geographic region.
- We would like to know the top quintile of wealth, by birth cohort.
In this module, we will go over how to calculate these statistics using the fake 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:
%%stata
*
clear * cd " "
"fake_data.dta", clear use
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 a function to easily compute such statistics. The key to this analysis is the pre-command by
. A pre-command is simply a prefix that tells Stata how we want it to run the command. In the case of by
, we tell Stata to run the command on the subsets of data. The only requirement to using this pre-command is to ensure that the data is sorted the correct way.
Let’s take a look at our data by using the browse
command we learned in Module 5.
%%stata
%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. We can see this error when we run the command below.
%%stata
//recall that capture drop tells Stata to ignore any errors if var_one does not exist, and to drop it if it does
capture drop var_one = 1 by year: generate var_one
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
pre-command as follows.
%%stata
sort year
Let’s take a look at our data now.
%%stata
%browse 10
Let’s try the command above again, now with the sorted data.
%%stata
capture drop var_one = 1 by year: generate var_one
Now that the data is sorted by year, the code works!
We could have also used the pre-command bysort
instead of sorting the data with sort
and then using by
. 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 (by workerid), and generate our new variable again.
Stata also lets us sort by two variables. The following block of code tells Stata to first sort
the data by workerid, and then within each workerid, to sort the data by year.
%%stata
sort workerid year
%%stata
capture drop var_one = 1 bysort year: generate var_one
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 summarize
command.
%%stata
summarize 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 two newvariables: one that is the observation number and one that is the total number of observations.
%%stata
capture drop obs_number = _n
generate obs_number
capture drop tot_obs= _N generate tot_obs
%%stata
%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. Let’s check that below:
%%stata
capture drop obs_number = _n
bysort workerid: generate obs_number
capture drop tot_obs= _N bysort workerid: generate tot_obs
%%stata
%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:
%%stata
if tot_obs==8 keep
%%stata
%browse 10
7.3 Generating Variables Using Extended Generate
The command egen
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.
%%stata
*
clear "fake_data.dta", clear use
%%stata
capture drop avg_earnings= mean(earnings) bysort workerid: egen avg_earnings
%%stata
capture drop total_earnings= total(earnings) bysort workerid: egen 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, we can see 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. We do this below by taking the sum of start_year, region, and treated.
%%stata
cap drop sum_of_vars= rowtotal(start_year region treated) egen sum_of_vars
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 when using generate
, but not when using egen
.
Just as with sort
, we can also use by
with multiple variables. Doing so tells Stata to run the command over all combinations of subgroups. Here will use year and region in one command. This tells Stata to generate a new variable for each year-region combination.
%%stata
capture drop regionyear_earnings= total(earnings) bysort year region : egen regionyear_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. These weights allow us to manipulate our data to better reflect the true composition of the data when the authorities 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)
We can find a full list of possible statistics that collapse
can take by running the command help collapse
. We 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 (sample_weight). 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 the average earnings, the variance of earnings, and the total employment. We will have three new variables: avg_earnings, sd_earnings, tot_emp. We write the following:
%%stata
= earnings (sd) sd_earnings = earnings (count) tot_emp = earnings [pweight = sample_weight], by(region year) collapse (mean) avg_earnings
%%stata
%browse 10
collapse
, Stata will produce a new data set with the results, and in the process it drops the data set that was loaded at the time the command was run. If we need to keep the original data, be certain to save the file before running 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.
%%stata
*
clear
"fake_data.dta", clear use
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
. wide
data is cross-sectional in nature, whereas long
is the usual panel.
Suppose we want to record the earnings of workers while keeping the information across years. This entails transforming our panel data into a cross sectional data set. We want one observation per worker, where each observation has all of the years. This is a wide
transformation.
%%stata
reshape wide earnings region age start_year sample_weight quarter_birth, i(workerid) j(year)
%%stata
%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 will use the reshape long
command. Note that to do this, we need to specify the prefix variables. These are formally known as stubs
in Stata. They are the variables that all share the same prefix (in this case, year), that will be transformed into one variable. When we write j(year)
, Stata will create a new variable called year.
%%stata
long earnings region age start_year sample_weight, i(workerid) j(year) reshape
%%stata
%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.
%%stata
if !missing(earnings) keep
%%stata
%browse 10
7.6 Errors
7.6.1. Sort
To develop group-compounded variables, we first need to ensure that we sort the observations by the variable. Not sorting the obserations will return an error code.
%%stata
capture drop var= _n by sex: generate var
The correct method of of generating compounded variables is below:
%%stata
capture drop var= _n bysort sex: generate var
Take a look at it below:
%%stata
summarize var
7.6.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.
%%stata
*
clear "fake_data.dta", clear use
%%stata
reshape wide earnings sex, i(year) j(workerid)
%%stata
reshape error
Can you tell what the error is here?
7.7 Wrap Up
In this module, we have covered some very useful skills that will be useful for exploring data sets. Namely, these skills will help us both prepare data for empirical analysis (i.e. turning cross sectional data into panel data) and create summary statistics that illustrate our results. In the next module, we will look at how to work with multiple data sets simultaneously and merge them into one.
7.8 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 data set |
_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 data set of a summary of statistics |
reshape |
It converts data from wide to long and vice versa |
References
Reshape data from wide format to long format
(Non StataCorp) How to group data in STATA with SORT and BY Syntax for pre-commands