How to create the average value of a variable in the respective quarters over the past three years for each firm?

[Juan Qin]

Here is part of my dataset:
gvkey(firm_id) fyear(year) qarter asset_growth(variable value)
001004 1995 1
001004 1995 2
001004 1995 3
001004 1995 4
001004 1996 1
001004 1996 2
001004 1996 3
001004 1996 4
001004 1997 1
001004 1997 2
001004 1997 3
001004 1997 4
001004 1998 1
001004 1998 2
001004 1998 3
001004 1998 4
......... ...... .
........ ....... .
001009 1995 1
001009 1995 2
001009 1995 3
001009 1995 4
001009 1996 1
001009 1996 2
001009 1996 3
001009 1996 4
001009 1997 1
001009 1997 2
001009 1997 3
001009 1997 4
001009 1998 1
001009 1998 2
001009 1998 3
001009 1998 4
......... ...... .
........ ....... .
What I need do is to use stata to calculate the mean of asset_growth in the respective quarters over the past three years for each firm. However, I don't know how to do it? Is there anyone can help me with this problem? Thank you so much in advance!

[Liu Qiang]

Hello Juan, I think what you really need is for the past four years for each firm.
BTW, please use -dataex- to post your example data in convenience for others to help you.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey int id double(fyear quarter asset_growth)
"001004" 1004 1995 1   -.3648016869178421
"001004" 1004 1995 2   -.2522016094865251
"001004" 1004 1995 3    .4941031037819322
"001004" 1004 1995 4   .15029882659317823
"001004" 1004 1996 1   .18771839177797567
"001004" 1004 1996 2   -.8144607412906152
"001004" 1004 1996 3  -1.9008214881049066
"001004" 1004 1996 4    .4928040564424545
"001004" 1004 1997 1  -.07692058928300574
"001004" 1004 1997 2   -.8163408940231329
"001004" 1004 1997 3  -2.6578321637770794
"001004" 1004 1997 4   -2.225325079192223
"001004" 1004 1998 1  -.20081293663068947
"001004" 1004 1998 2    .2954932607466491
"001004" 1004 1998 3   1.2529256226509002
"001004" 1004 1998 4  -.22590911224461244
"001009" 1009 1995 1   -.6409772469106287
"001009" 1009 1995 2   -1.931203609424539
"001009" 1009 1995 3  -1.2375450707522175
"001009" 1009 1995 4   -.3406488341284555
"001009" 1009 1996 1  -2.6617512422183576
"001009" 1009 1996 2   1.5555497771789357
"001009" 1009 1996 3   .49947017799360693
"001009" 1009 1996 4   1.3961515016061157
"001009" 1009 1997 1  -.20257820339769486
"001009" 1009 1997 2    .8563413712704551
"001009" 1009 1997 3   .24980499258892616
"001009" 1009 1997 4  -2.7018666293287157
"001009" 1009 1998 1 -.029287010057273445
"001009" 1009 1998 2   1.1860799673286575
"001009" 1009 1998 3    1.508307900515411
"001009" 1009 1998 4    2.188582517452786
end

Or you can also post a simulation data like:

clear all
version 15.0
set obs 32
set seed 12345
gen str gvkey="001004" in 1/16
replace gvkey="001009" in 17/l
destring gvkey, force g(id)
bys id: gen fyear=floor((_n-0.1)/4)+1995
bys id fyear: gen quarter=_n
gen asset_growth=invnormal(uniform())
collapse (mean) asset_growth, by(gvkey quarter)

[Nick Cox]

With rangestat from SSC, I can do this

gen qdate = yq(fyear, quarter) 
format qdate %tq 
rangestat (count) count=asset_growth (mean) mean=asset_growth, int(qdate -16 -1) by(id)

I don't know why Liu Qiang thinks it's the last four years needed, but choose your own interval.

Searching the forum for rangestat will yield other examples.

[Juan Qin]

Hello Qiang, thank you so much for your reply. I am sorry for the late feedback. Your code looks good. I tried it, but it doesn't work for my question. Please allow me to use the dataset you provided as a example to illustrate what I am looking for. What I need is the average of asset_growth in the respective quarters over the past three years for each firm. For example, for 1998, to determine the average of asset_growth in each quarter over the past three year for firm 1004, I need compute the mean of asset_growth based on the asset_growth value in quarter1,2,3,4 of year 1995,1996, and 1997 from firm 1004. When I tried your code, the variable "fyear" dissapeared finally. However, I need keep all this variables. I don't know how to do it with stata. Could you still help me with this? Thank you for your help and patience!

[Juan Qin]

Hello Nike, I will try the code you provided as well. Thank you so much!

[Liu Qiang]

Hello Juan, I am so sorry that I misunderstood your intention before Nick Cox offered his different solution. I think Nick's thought is with you, right?

[Nick Cox]

It may be that

interval(fyear -3 -1)

is precisely what you want.

[Juan Qin]

Hello Nick and Qiang, thank you so much for your help. The code provide by Nick solve my problem. I really appreciate that!