Average and Median values by group

[Yahya Ghazali]

Dear All

Will appreciate if you can help me on the following query.

The following data shows firms (ID), year , reform year (SSRCOMPLETEYR) and the variable NTS (ownership proportion).

I want to create variables, i.e. the mean and median of NTS with respect to the reform year. For instance, for firms which have reform year (SSRCOMPLETEYR) 2005, then I want the mean and median values of NTS for all these firms for three-years prior (2002), two-years prior(2003) and one-year prior (2004) to the reform year (2005). I also want the mean and median values of NTS for all these firms t+1, t+2 and t+3 where t is the reform year (SSRCOMPLETEYR).

* Example generated by -dataex-. To install: ssc install dataex
clear
input long ID float YEAR int SSRCOMPLETEYR float NTS
2 2003 2005 .1583336
2 2004 2005 .14580873
2 2005 2005 .13422763
2 2006 2005 .129922
2 2007 2005 .02426268
2 2008 2005 .026378496
2 2009 2005 .002197306
2 2010 2005 .0017612013
2 2011 2005 .0016758556
2 2012 2005 .001673647
2 2013 2005 .0021632616
2 2014 2005 .0014382304
4 2014 2006 .001205394
6 2003 2006 .37771195
6 2004 2006 .37771195
6 2005 2006 .37771195
6 2006 2006 .2533447
6 2007 2006 .13797276
6 2008 2006 .08924162
6 2009 2006 .02732855
6 2010 2006 .02611745
6 2011 2006 .021394266
6 2012 2006 .01581895
6 2013 2006 .0087063005
6 2014 2006 .008220699


Many thanks

Yahya Ghazali

[Clyde Schechter]

I'm not 100% sure I understand what you want to calculate here, but I think you are asking for the means and medians of NTS during the three years preceding SSRCOMPLETEYR, and then, separately, the means and medians of NTS during the three years following SSRCOMPLETEYR. If so, this should do it:

gen low = SSRCOMPLETEYR - 3
gen high = SSRCOMPLETEYR - 1
rangestat (mean) NTS (median) NTS, interval(YEAR low high) by(ID)
rename (NTS_*) =_before
replace low = SSRCOMPLETEYR + 1
replace high = SSRCOMPLETEYR + 3
rangestat (mean) NTS (median) NTS, interval(YEAR low high) by(ID)
rename (NTS_*) =_after

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer, and is available from SSC.

[Yahya Ghazali]

Dear Clyde

Thank you for this. It works perfectly.

However, what I require is to create variables which set the year of SSRCOMPLETEYR at the firm level to be year zero, then to take measures at years -1, -2, +1, +2. For instance, firms with SSRCOMPLETEYR 2005, I want NTS values at years -1 -2 +1 +2 and then take average across all firms. Similarly, firms with SSRCOMPELETEYR 2006, I want NTS values at years -1 -2 +1 +2 and then take average across all firms.

Many thanks

Yahya Ghazali

[Clyde Schechter]

Well, this is very different from what I saw in #1. I hope I'm understanding you correctly.

capture program drop one_complete_year
program define one_complete_year
    summ NTS if YEAR != rr_SSRCOMPLETEYR, detail
    gen mean = r(mean)
    gen median = r(p50)
    keep in L
    foreach v of varlist ID YEAR SSRCOMPLETEYR NTS {
        replace `v' = rr_`v'
    }
    exit
end

gen low = SSRCOMPLETEYR - 2
gen high = SSRCOMPLETEYR + 2

rangerun one_complete_year, interval(YEAR low high) sprefix(rr_)

-rangerun- is by Robert Picard and is available from SSC. It requires -rangestat- which, presumably, you already have installed.