Mean correcting a panel data set

[Jamie Hamilton]

Simple coding question. How do I mean correct a set of panel data. The example here is a set of values to TSR by Year and Company, and the companies fall into categories in the variable Category

The coding I was trying is:

gen MeanTSR = 0
forvalues Y = 2005/2018 {

bysort Category: egen meanTSR`Y' = mean(TSR) if Year == Y
replace MeanTSR = meanTSR`Y' if Year == Y

}

gen corrTSR = TSR - MeanTSR

What I find is that I get the same value for all years, as if the if Year == Y was not effective.
There must be a better "Stata" way to do this.

Many thanks
Jamie

[Carlo Lazzaro]

Jamie:
are you sure that you do not want something along the following lines?

. use "http://www.stata-press.com/data/r16/nlswork.dta", clear
(National Longitudinal Survey.  Young Women 14-26 years of age in 1968)

. bysort id: egen wanted=mean( ln_wage)

. gen corr_ln_wage=ln_wage-wanted

. sum wanted corr_ln_wage ln_wage

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
      wanted |     28,534    1.674907    .3780522          0   3.912023
corr_ln_wage |     28,534   -2.83e-10      .29266  -2.082629   3.108763
     ln_wage |     28,534    1.674907    .4780935          0   5.263916

.

[Jamie Hamilton]

No - not quite!

Taking your dataset, the equivalent I would be wanting would be to correct ln_wage by, say, the average of all id's for a given age and a given grade.
wanted = ln_wage (id, age) - {mean of ln_wage} (age, grade)

Making sense?

Jamie

[Carlo Lazzaro]

Jamie:
see:

use "http://www.stata-press.com/data/r16/nlswork.dta", clear
. g TSR=runiform()
. gen MeanTSR = 0
. forvalues Y = 70/72 {
  2. bysort idcode: egen meanTSR`Y' = mean(TSR) if year ==`Y'
  3. replace MeanTSR = meanTSR`Y' if year == `Y'
  4.  }
. gen corrTSR = TSR - MeanTSR

.

[Nick Cox]

#1 can be corrected (some local macro punctuation is missing) and simplified to

bysort Category Year: egen meanTSR = mean(TSR)

A reason why you did not find that

if Year == Y

gives specific results for Year is that you missed out the local macro punctuation around Y. If so, then it's likely that

if Year == Year

is the interpretation of your qualifier, which necessarily has no effect. It's always true.

Note that local macros are irrelevant here, as a double loop over your grouping variables is automatic with egen.

[Jamie Hamilton]

This is great - for which many thanks!

I found another solution meantime:

egen Concat = group(Year Category), label
bysort Concat: egen meanTSR = mean(TSR)
gen corrTSR = TSR - meanTSR

Which is simply a more verbose way of doing what is so simple!

Jamie