Panel Data: Yearly Average?

[Jacques Picard]

Hi Guys,

i am dealing with a panel data problem.
I have a data set over 25 years and several companies.

What do I want to do now? Well i want to have the average value of a ratio over all firms for every year.
So that i can create a graph where you can look at the developement of that specific average ratio over all firms for every years.
I searched through the help files and the Internet and I did not get the answer until now.

I tried for example:

egen average_CAPnorm = mean(CAPnorm) if inrange(year,1989,2015)

But that only gives me the mean over all firms and all periods and no yearly average over all firms.
You would help me so much!

[Jorrit Gosens]

You probably want:

bysort year: egen average_CAPnorm = mean(CAPnorm)

[Carlo Lazzaro]

Jacques.
welcome to the list.
Are you looking for something along the following lines?

use http://www.stata-press.com/data/r14/nlswork.dta
egen MEAN=mean(ln_wage), by(year)
tabstat MEAN, stats(count mean sd p50 min max) by(year)

PS: Crossed in the cyberspace with Jorrit's reply.

[Jacques Picard]

Thank you Carlo and Jorrit!
Your code works perfectly Jorrit, that was the missing bit I searched for the command.

[Cristian Scurtu]

Regarding a similar issue as Jacques had. What will be the code to calculate the average of each company, in every year. Thank you!

[Raffaele Grotti]

You only have to add the variable identifying the company within the -by()-, i.e. -by(year company)-

[Irfan Ali]

Hi,
I have panel data and want to generate a variable which shows yearly average ( from 2007 tp 2016) for firms with women and without women using Womendummy. I have tried the following code it works but not in a way i want as I want to generate the plot as well.

"gen cashratio= mean (CASH_w), by( year WOMENDUMMY)"

Thanks in advance.
Irfan

[Nick Cox]

I doubt that works at all as mean() is an egen function and will not work with generate. But assuming that is a slip, no egen function I know of promises a graph too, certainly not mean().

A graph could be produced by a command like

line cashratio year if WOMENDUMMY == 0 | line cashratio year if WOMENDUMMY == 1

[Irfan Ali]

Thanks again Nick.

You're right, it was a slip. Similary, i forgot to mention that I used "tsline cashratio" for graph. Now, I tried the code as you've written but it says "line not found". I am using the latest version of stata. Do i have to install it?

[Nick Cox]

No. line is an official command. My typo, sorry: | should be ||

[Christos Tsoukalis]

Hi,
I am also working with panel data and i want to take yearly averages to run some regressions on the time series dimension. I used the by(year) in the end of the command and it worked perfectly. However, this created repeated observations (i.e. i now have the 2008 average for example in the entries corresponding to all the entities for 2008). This gives me highly statistically significant results in my regressions. Is this ok or do i need to collapse the dataset? Thank you very much

[Carlo Lazzaro]

Christos:
you may want to try something like:

. set obs 10
number of observations (_N) was 0, now 10

. g id=1 in 1/5
(5 missing values generated)

. replace id=2 if id==.
(5 real changes made)

. g A=2008

. g B=runiform()

. bysort id: g test=1 if _n==1
(8 missing values generated)

. bysort id: sum B if test==1

---------------------------------------------------------------------------------------------------------------------
-> id = 1

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
           B |          1    .2047095           .   .2047095   .2047095

---------------------------------------------------------------------------------------------------------------------
-> id = 2

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
           B |          1    .3913819           .   .3913819   .3913819

: