Create laggings

[Pepijn Peters]

Hello,

I have a dataset of 1,791 firm-year observations. Now, I would like - for each firm-year observation - to incorporate the earnings number of the previous 5 years.

So, for example, I have this:
Company | fyear | earnings (on t=fyear)
x | 2018 | ...
x | 2017 | ...
y | 2018 | ...
y | 2017 | ...
y | 2016 | ...
y | 2015 | ...
z | 2016 | ...
z | 2015 | ...
z | 2014 | ...

What I want to get is this:

Company | fyear | earnings (on t=fyear) | earnings t=-1 | earnings t=-2 | earnings t=-3 | earnings t=-4 | earnings t=-5

__________________________________________________ __________________________________________

My questions is thus:
How can I merge COMPUSTAT's earnings output with my firm-year observations?

If you need more information, please feel free to ask!

Kind regards,
Pepijn

[Wouter Wakker]

I don't understand how this would involve merging, but if your dataset is as in your example you can tsset or xtset your data which allows you to use time-series operators (lags and leads). You can than create lagged variables in this way:

tsset id year
gen L1_earnings = L.earnings
gen L2_earnings = L2.earnings
etc.

Note that you can also use time-series operators directly in other commands like regressions, so it may not be necessary at all to create these variables if that is what you're after.

[Pepijn Peters]

Hi Wouter,

Thanks for your response!

If I type in the code tsset CIK fyear (CIK = company identifier) I receive the error 'repeated time values within panel'.

So I have all the information for one firm-year observation in one row.
Now I want to measure earnings volatility for the prior 5 years (for every firm-year observation). Therefore, I would like to add 5 columns to each row for t-1, t-2, t-3, t-4, and t-5 to be able to calculate the volatility during those years.

Unfortunately, this is not working yet, as I don't know how to get from compustat year-information, to this "merged" dataset with my firm-year observations.

Hope this clarifies.

[Nick Cox]

#1 tells us that you have (firm, year) observations but clearly not. You need to address that before anything else can be suggested.

https://www.stata.com/support/faqs/d...d-time-values/ may help.

[Pepijn Peters]

Hi Nick,

Thanks for your response.

I have this panel data, but what should I address? In other words, what is wrong with this for statistical analysis? The earnings volatility I need as a control variable, so for each row (a year for a given firm) I need to calculate the volatility based on the prior 5 years of that rows' year.

[Nick Cox]

Did you read the FAQ link? You can't apply lag operators if tsset didn't work. So, why is there a message of repeated time values?

[Phil Bromiley]

Compustat sometimes reports more than one observation for a firm-year. Look carefully at the documentation and the data with duplicates (see the duplicates procedure) and decide how you're going to choose which observation to keep if there are more than one for a given firm-year. Often, the observations are almost identical so it may not matter which you drop. After you get rid of the excess observations, then you should be able to xtset your data without a problem.