Library Help: Teaching and learning

When data are exported from a database they are not always in the format you need for analysing. In this post we will explore how you can convert long to wide data in Stata. You could easily adapt the process to convert wide to long data.

In this illustration, there are two companies with IDs 10500 and 222622. There is a daily return price for each company. You may get 'long' data from CRSP and need to reshape it to 'wide' data for analysis.

The stages are as follows:

1. Importing data to Stata
2. Apply the reshaping command
3. Export to Excel

1. Import data to Stata

In Excel, select only the essential variables (unique id, company identifier and target variable). Here the unique id is the ‘date’, with ‘id’ being the company identifier and ‘return’ as the target variable. Save as a .csv file and imported into Stata.

1. In Stata, select ‘File’ > ‘Import’ > ‘Text data (delimited)’. 
2. Click on ‘Browse’ and select the file of interest, then click ‘OK’.

2. Apply the reshaping command

Type code in the Command window to modify the imported data set. Use the reshape wide command, followed by these parameters:

• The name of the variable you wish to widen return then a comma,. This will form the first half of the new column names.
• The new unique identifier, i(date). This will be the new first column.
• The target variable that will be used to widen the data, j(id). This is the old unique identifier and will form the second half of the new column names.

reshape wide return i(date) j(id) #reshapes the data
list #shows the new data in stata

After this is typed, you will get a summary of what just happened.

If the text is in red, an error occurred which maybe due to other variables that were not mentioned in the reshape command.

You can list more than one variable to be reshaped; the command could for example be:

reshape wide return price, i(date) j(id) 
#where 'return' and 'price' are the target variables

3. Export to Excel