Standard Deviation Excluding Focal Firm

[Attaullah Shah]

Hi All
Consider a dataset;

ID	Year	ROA
1	2001	.01
1	2002	.02
1	2003	.03
2	2001	.2
2	2002	.3
2	2003	.4
3	2001	1
3	2002	2
3	2003	3

How can we find standard deviation of ROA for the rest of the firms when it is being calculated for ID==1 i.e. standard deviation of ROA for ID==2, and ID==3. Similarly, for ID==2, the sd of ROA for ID ==1 and ID==3 will be calculated, and so on. Thanks.

[Roberto Ferrer]

Consider something along the lines of

clear
set more off

input ///
ID     Year     ROA
1     2001     .01
1     2002     .02
1     2003     .03
2     2001     .2
2     2002     .3
2     2003     .4
3     2001     1
3     2002     2
3     2003     3
end

gen sdexcl = .
forvalues i = 1/3 {
    quietly summarize ROA if ID != `i'
    quietly replace sdexcl = r(sd) if ID == `i'
}

list

The result

     +----------------------------+
     | ID   Year   ROA     sdexcl |
     |----------------------------|
  1. |  1   2001   .01   1.127386 |
  2. |  1   2002   .02   1.127386 |
  3. |  1   2003   .03   1.127386 |
  4. |  2   2001    .2   1.255452 |
  5. |  2   2002    .3   1.255452 |
     |----------------------------|
  6. |  2   2003    .4   1.255452 |
  7. |  3   2001     1    .166012 |
  8. |  3   2002     2    .166012 |
  9. |  3   2003     3    .166012 |
     +----------------------------+

shows that 1.127 is the stand. dev. when excluding ID == 1. Likewise, 1.255 when excluding ID == 2, and so on.

[Attaullah Shah]

Thanks Roberto Ferrer. Since your code is looping through observations, how can we handle the following problems:

1. I have ID more than 40000, and years more than 17 and industries more than 60, I need to find this standard deviation for each firm separately in each industry in a rolling window of 4 years, will the loop work?
2. I have gaps in ID sequence, how can account for that?

[Attaullah Shah]

After some experimentation, I have solved my problem. I am posting my solution in case someone needs it in future. The code takes time to complete. I would love to recieve suggestions on how to make it more efficient.

 set more off
levelsof loc, local(country)
foreach c of local country{
levelsof industry if loc=="`c'", local(ind) // loc is string that identifies each country
foreach i of local ind{
levelsof gvkey if industry==`i' & loc=="`c'", local(gk)
foreach g of local gk{
        forval y=1997/2009{
        quietly summarize ROA if gvkey!= `g' & fyear>`y'-1 & fyear<`y'+4 & loc=="`c'" & industry==`i'
        quietly replace SDx= r(sd) if gvkey==`g' & fyear==`y'+4
      }
}
}
}