Help in 'moving common' command

[rogers wonng]

I have an unbalanced panel, say firms over years, each firm has a variable of interest. I would like to do the following: for each year t, consider t+1, compare these two years and get the common firms. Calculate the mean of variable of interest cross sectionally for these common firms at year t. Next, for year t+1, compare it with t+2, get another common set of firms, again, calculate the mean of the new common set of firms at year t+1. For example, as follows.
For year 2008, compare it with 2009. I have common firms: C, D. then I calculate C,D's mean (z+m)/2 for year 2008.
For year 2009, compare it with 2010. I have common firms: C, F. then I calculate C, F's mean (n+w)/2 for year 2009.
For year 2010, compare it with 2011. I have common firms: F, G. then i calculate F, G's mean (r+v)/2 for year 2010.

I understand this is a rather simple algorithm, but i didnt find a good way to do it in stata. Hope you could help. Many thanks.

year	firm id	variable of interest
2008	A	x
2008	B	y
2008	C	z
2008	D	m
2009	C	n
2009	D	q
2009	F	w
2010	C	t
2010	F	r
2010	G	v
2011	F	u
2011	G	p
2011	H	j

[Nick Cox]

Schematic examples can be helpful, but realistic examples are better still.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str1 firmid float whatever
2008 "A"  1
2008 "B"  2
2008 "C"  3
2008 "D"  4
2009 "C"  5
2009 "D"  6
2009 "F"  7
2010 "C"  8
2010 "F"  9
2010 "G" 10
2011 "F" 11
2011 "G" 12
2011 "H" 13
end

encode firmid, gen(id)
tsset id year
gen tag = F.whatever < . & whatever < . 
egen wanted = mean(cond(tag, whatever, .)) , by(year)

sort year firmid 

list, sepby(year)

    +----------------------------------------------+
     | year   firmid   whatever   id   tag   wanted |
     |----------------------------------------------|
  1. | 2008        A          1    A     0      3.5 |
  2. | 2008        B          2    B     0      3.5 |
  3. | 2008        C          3    C     1      3.5 |
  4. | 2008        D          4    D     1      3.5 |
     |----------------------------------------------|
  5. | 2009        C          5    C     1        6 |
  6. | 2009        D          6    D     0        6 |
  7. | 2009        F          7    F     1        6 |
     |----------------------------------------------|
  8. | 2010        C          8    C     0      9.5 |
  9. | 2010        F          9    F     1      9.5 |
 10. | 2010        G         10    G     1      9.5 |
     |----------------------------------------------|
 11. | 2011        F         11    F     0        . |
 12. | 2011        G         12    G     0        . |
 13. | 2011        H         13    H     0        . |
     +----------------------------------------------+

[rogers wonng]

Hi Nick, it works greatly! Thank you so much! I previously was thinking of a loop within loop way, but yours is much simpler and efficient to implement! Thanks again

[rogers wonng]

Schematic examples can be helpful, but realistic examples are better still.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str1 firmid float whatever
2008 "A" 1
2008 "B" 2
2008 "C" 3
2008 "D" 4
2009 "C" 5
2009 "D" 6
2009 "F" 7
2010 "C" 8
2010 "F" 9
2010 "G" 10
2011 "F" 11
2011 "G" 12
2011 "H" 13
end

encode firmid, gen(id)
tsset id year
gen tag = F.whatever < . & whatever < .
egen wanted = mean(cond(tag, whatever, .)) , by(year)

sort year firmid

list, sepby(year)

+----------------------------------------------+
| year firmid whatever id tag wanted |
|----------------------------------------------|
1. | 2008 A 1 A 0 3.5 |
2. | 2008 B 2 B 0 3.5 |
3. | 2008 C 3 C 1 3.5 |
4. | 2008 D 4 D 1 3.5 |
|----------------------------------------------|
5. | 2009 C 5 C 1 6 |
6. | 2009 D 6 D 0 6 |
7. | 2009 F 7 F 1 6 |
|----------------------------------------------|
8. | 2010 C 8 C 0 9.5 |
9. | 2010 F 9 F 1 9.5 |
10. | 2010 G 10 G 1 9.5 |
|----------------------------------------------|
11. | 2011 F 11 F 0 . |
12. | 2011 G 12 G 0 . |
13. | 2011 H 13 H 0 . |
+----------------------------------------------+

Hi Nick, it works greatly! Thank you so much! I previously was thinking of a loop within loop way, but yours is much simpler and efficient to implement! Thanks again