07 - Conducting Within Group Analysis

In this notebook, we look at within-group analysis. We see how to summarize data for subgroups, how to generate new variables among subgroups, and how to reshape out data.

Prerequisites

1. Be able to effectively use Stata do-files and generate log-files.
2. Be able to change your directory so that Stata can find your files.
3. Import data sets in csv and dta format.
4. Save data files.

Learning Outcomes

1. Create new variables using the command egen.
2. Know when to use the pre-command by and when to use bysort.
3. Use the command collapse to create a new data set of summary statistics.
4. Change a panel data set to a cross-sectional data set using the command reshape.

7.0 Intro

import stata_setup
stata_setup.config('C:\Program Files\Stata18/','se')

>>> 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 " "
use "fake_data.dta", clear

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

capture drop var_one //recall that capture drop tells Stata to ignore any errors if var_one does not exist, and to drop it if it does
by year: generate 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 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 
by year: generate 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 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 
bysort year: generate 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 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 
generate obs_number = _n 

capture drop tot_obs
generate tot_obs = _N

%%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 
bysort workerid: generate obs_number = _n 

capture drop tot_obs
bysort workerid: generate tot_obs = _N

%%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

keep if tot_obs==8

%%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.

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 *
use "fake_data.dta", clear

%%stata

capture drop avg_earnings
bysort workerid: egen avg_earnings = mean(earnings)

%%stata

capture drop total_earnings
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, 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
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 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
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. 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

collapse (mean) avg_earnings = earnings (sd) sd_earnings = earnings (count) tot_emp = earnings [pweight = sample_weight], by(region year)

%%stata

%browse 10

Warning: When we use 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 *

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. 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)

Warning: This command acts on all of the variables in our data set. If we don’t include them in the list, Stata will assume that they do not vary across i (in this case workerid). If we don’t check this beforehand, we may get an error message!

%%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

reshape long earnings region age  start_year sample_weight, i(workerid) j(year) 

%%stata

%browse 10

To retrieve the original data set, we get rid of such observations with missing values.

%%stata

keep if !missing(earnings)

%%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
by sex: generate var = _n

The correct method of of generating compounded variables is below:

%%stata

capture drop var
bysort sex: generate var = _n

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 *
use "fake_data.dta", clear

%%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