Calculating standard deviation and mean for each firm in panel data

[Karim Syed]

Hi all
I have a panel dataset with 100 firms and 5 years. For my variable named ebit, I want to calculate standard deviation for each firm separately and save them into a new variable called ebit_sd. Similarly, for another variable sales, i want to calculate mean growth for five years and have it saved as 5_year_mean_sales_growth.

Where sales growth is calculated as ((sales - sales t-1) / sales t-1 )* 100.

After I do this, my dataset should look like as follows:

Firm	Year	ebit	ebit_sd	sales	5_year_mean_sales_growth
1	2000	xxx	a	xxx	d
1	2001	xxx	a	xxx	d
1	2002	xxx	a	xxx	d
1	2003	xxx	a	xxx	d
1	2004	xxx	a	xxx	d
2	2000	xxx	b	xxx	e
2	2001	xxx	b	xxx	e
2	2002	xxx	b	xxx	e
2	2003	xxx	b	xxx	e
2	2004	xxx	b	xxx	e
3	2000	xxx	c	xxx	f
3	2001	xxx	c	xxx	f
3	2002	xxx	c	xxx	f
3	2003	xxx	c	xxx	f
3	2004	xxx	c	xxx	f

Note: a b c represent standard deviation for firm 1 2 and 3 respectively, and d e f represent mean growth rate for firm 1 2 and 3 respectively.

Kindly help. Thanks in advance.

[Clyde Schechter]

egen ebit_sd = sd(ebit), by(firm)

xtset firm year
gen growth = 100*(sales-L1.sales)/(L1.sales)
tsegen five_year_mean_sales_growth = rowmean(L(1/5).growth)

Notes:

1. Do read the manual sections on -egen-. It is chock full of useful functions, and you will need to be familiar with it to work effectively with Stata.
2. -tsegen- is a program written by Robert Picard and Nick Cox, not part of official Stata. If you do not have it installed, run -ssc install tsegen- to get it. Then read the help file.
3. I can't tell if your request for a five year mean refers to the preceding 5 years, the current year plus the preceding four, or some other five year period. The code above calculates the mean for the preceding five years. You can modify it according to your actual intention.

[Nick Cox]

Clyde gives excellent advice as usual.

Karim: On 9 February you wrote "I am going to install dataex and will surely follow that format in future" You have asked several questions and got detailed answers. It's now time to raise the game a little and help us a little more. .

[Karim Syed]

I am heartily thankful to both of you Clyde and Nick for your suggestions and patience. Yes, Nick I have already installed dataex and will surely use that one in my future communications. The question above however was of general nature so I prefer to use this format. Many thanks once again.
Regards

[Nick Cox]

In turn we prefer concrete examples we can copy and paste and experiment with using code we write.

As Clyde pointed out, your question left quite unclear behind all those letters exactly what your definition was for growth rates.

People are happy to help, but there's good reason for our requests.

Note: Trusting your detail of 5 years, you can only calculate growth for 4 years, as nothing is known for 1999.

[Karim Syed]

Sure Nick, I shall be careful in future in presenting and writing queries. Thanks for all your comments and suggestions.
Best Regards

[mohina saxena]

Hello all,

I want to create a variable that is business risk, following a paper this variable is measured as:

"The deviation of each company’s Return on Total Assets is computed from the sample average for each of the six consecutive years of firm observations during the period 2004–2009." The standard deviation of this measure is then considered as the value of risk.

Riski = Sqrt[1/n-1 Sigma t= 2004-2009 {ROTAit-1/n(sigma t= 2004-2009 ROTAit)}2], n =6

I have an unbalanced panel dataset (Firm id= Unique_Identifier] for 2001-2017 from several industries. I use the following command for my dataset to generate the above measure

egen ebit_ROTA = sd(ROTA), by(Unique_Identifier)

With the above code, each firm has same value of risk [that varies for each firm] but remains same for all the years. So while performing FE since there is no within variation this variable omits out. I doubt if I have correctly understood the variable's construction. Can someone please help me out here. Attaching below the issue:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year long Unique_Identifier double(NetSales ROTA) float ebit_ROTA
2001 1    40.3  -5.11 14.865506
2002 1   135.5 -10.68 14.865506
2003 1    77.2  -23.8 14.865506
2010 1     3.8 -17.18 14.865506
2011 1     4.3 -30.09 14.865506
2012 1    15.5 -11.14 14.865506
2013 1    35.9   6.71 14.865506
2015 1    64.2   2.78 14.865506
2016 1   106.9    6.8 14.865506
2017 1   155.2  16.75 14.865506
2001 2    3552   6.05  4.260055
2002 2  4129.9   8.88  4.260055
2003 2  4761.9  14.67  4.260055
2004 2  6145.4  16.03  4.260055
2005 2  6835.8  12.32  4.260055
2006 2  7547.7   8.81  4.260055
2007 2  9267.2  13.73  4.260055
2008 2 10657.5  12.45  4.260055
2009 2 12900.3    6.3  4.260055
2010 2 15717.4  13.64  4.260055
2011 2 19106.1  14.64  4.260055
2012 2 23471.1  13.15  4.260055
2013 2 27784.6   13.2  4.260055
2014 2 32368.8  16.05  4.260055
2015 2 30767.3   7.19  4.260055
2016 2 28223.4   2.31  4.260055
2017 2 30977.7    4.7  4.260055
2002 3    20.4   8.38 3.6527724
2003 3    21.7  14.33 3.6527724
2004 3    21.1   3.56 3.6527724
2005 3    18.8   1.29 3.6527724
2006 3    27.4   3.68 3.6527724
2008 3    37.1   9.97 3.6527724
2009 3    45.8   9.14 3.6527724
2010 3    60.6    6.8 3.6527724
2011 3    80.2   6.49 3.6527724
2012 3    49.8   3.33 3.6527724
2013 3   107.1   6.26 3.6527724
2014 3    67.5   3.02 3.6527724
2015 3    69.7   3.24 3.6527724
2016 3    65.4   1.84 3.6527724
2017 3    58.4   2.34 3.6527724
2001 4  2261.9  16.65  6.788861
2002 4  2681.7  18.48  6.788861
2003 4    2820  17.69  6.788861
2004 4  3050.7  15.65  6.788861
2005 4  3596.4  15.47  6.788861
2006 4  4608.9  15.29  6.788861
2007 4  5214.8  11.58  6.788861
2008 4    5565   5.95  6.788861
end
format %ty Year
label values Unique_Identifier UI1
label def UI1 1 "21_102524", modify
label def UI1 2 "21_102576", modify
label def UI1 3 "21_102816", modify
label def UI1 4 "21_103261", modify

Thanks a lot.