Expand data based on a time variable & extract information in panel data

[Ulrica He]

Dear Statalisters,

Below is my data (show the two rows), which includes gender, begin date and end date(for the manager's tenure in a particular fund.) I want to have add rows for different tenure in year_month. For example, the tenure is between 27-oct-99 and 30-aug-02, what I need is 1999m10 1999m11 1999m12, etc.

Data

fundid managername male_0_female_1_notsure_NA begdate enddate
FSUSA000FG A. Bruce Cleveland 0 31-Dec-97 30-Apr-08
FSUSA002NO A. Byron Nimocks 0 8-May-95 1-Mar-06


Code used:

expand mofd(enddate) - mofd(begdate) + 1
generate year_month = mofd(begdate)
format year_month %tm
bysort managername(fundid): replace year_month = year_month[_n-1]+1 if _n>1


Problems:

1. For some managers who worked in different fund at different or same time period, the year_month exceeds the tenure.

fundid managername male_0_female_1_notsure_NA begdate enddate year_month
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m2
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m3
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m4
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m5
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m6
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m7
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m8
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m9
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m10
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m11
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2010m12
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m1
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m2
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m3
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m4
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m5
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m6
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m7
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m8
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m9
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m10
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m11
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2011m12
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m1
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m2
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m3
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m4
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m5
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m6
FSUSA002EZ A. Douglas Rao 0 28-Feb-10 20-Jul-12 2012m7
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2012m8
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2012m9
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2012m10
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2012m11
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2012m12
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m1
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m2
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m3
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m4
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m5
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m6
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m7
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m8
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m9
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m10
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m11
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2013m12
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m1
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m2
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m3
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m4
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m5
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m6
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m7
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m8
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m9
FSUSA00359 A. Douglas Rao 0 17-May-10 20-Jul-12 2014m10
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2014m11
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2014m12
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m1
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m2
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m3
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m4
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m5
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m6
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m7
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m8
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m9
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m10
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m11
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2015m12
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m1
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m2
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m3
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m4
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m5
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m6
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m7
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m8
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m9
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m10
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m11
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2016m12
FSUSA0035N A. Douglas Rao 0 17-May-10 20-Jul-12 2017m1
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m2
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m3
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m4
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m5
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m6
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m7
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m8
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m9
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m10
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m11
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2017m12
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m1
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m2
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m3
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m4
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m5
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m6
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m7
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m8
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m9
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m10
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m11
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2018m12
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m1
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m2
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m3
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m4
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m5
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m6
FSUSA003ML A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m7
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m8
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m9
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m10
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m11
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2019m12
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m1
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m2
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m3
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m4
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m5
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m6
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m7
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m8
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m9
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m10
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m11
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2020m12
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m1
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m2
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m3
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m4
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m5
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m6
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m7
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m8
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m9
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m10
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m11
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2021m12
FSUSA003MM A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m1
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m2
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m3
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m4
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m5
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m6
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m7
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m8
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m9
FSUSA003T7 A. Douglas Rao 0 1-Feb-10 20-Jul-12 2022m10





2. I want to distinguish that at a certain time(year_month) for different fund, whether there is a female(1) or not (0).
However, I have no idea on this.

Could anyone help?
(If the dataset are required, please tell me.)

Lots of thanks in advance



Regards,
Ulrica

[William Lisowski]

Welcome to Statalist.

The following code corrects the one small misunderstanding in the bysort prefix to your replace command.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 fundid str14 managername float(begdate enddate)
"FSUSA002EZ" "A. Douglas Rao" 18321 19194
"FSUSA00359" "A. Douglas Rao" 18399 19194
"FSUSA0035N" "A. Douglas Rao" 18399 19194
"FSUSA003ML" "A. Douglas Rao" 18294 19194
"FSUSA003MM" "A. Douglas Rao" 18294 19194
"FSUSA003T7" "A. Douglas Rao" 18294 19194
end
format %td begdate
format %td enddate

expand mofd(enddate) - mofd(begdate) + 1
generate year_month = mofd(begdate)
format year_month %tm
bysort managername fundid: replace year_month = year_month[_n-1]+1 if _n>1

// list the first and last observation of each manager/fundid combination
bysort managername fundid: generate tolist = _n==1 | _n==_N
list fundid-year_month if tolist, sepby(managername fundid) abbreviate(20)

 list fundid-year_month if tolist, sepby(managername fundid) abbreviate(20)

     +------------------------------------------------------------------+
     |     fundid      managername     begdate     enddate   year_month |
     |------------------------------------------------------------------|
  1. | FSUSA002EZ   A. Douglas Rao   28feb2010   20jul2012       2010m2 |
 30. | FSUSA002EZ   A. Douglas Rao   28feb2010   20jul2012       2012m7 |
     |------------------------------------------------------------------|
 31. | FSUSA00359   A. Douglas Rao   17may2010   20jul2012       2010m5 |
 57. | FSUSA00359   A. Douglas Rao   17may2010   20jul2012       2012m7 |
     |------------------------------------------------------------------|
 58. | FSUSA0035N   A. Douglas Rao   17may2010   20jul2012       2010m5 |
 84. | FSUSA0035N   A. Douglas Rao   17may2010   20jul2012       2012m7 |
     |------------------------------------------------------------------|
 85. | FSUSA003ML   A. Douglas Rao   01feb2010   20jul2012       2010m2 |
114. | FSUSA003ML   A. Douglas Rao   01feb2010   20jul2012       2012m7 |
     |------------------------------------------------------------------|
115. | FSUSA003MM   A. Douglas Rao   01feb2010   20jul2012       2010m2 |
144. | FSUSA003MM   A. Douglas Rao   01feb2010   20jul2012       2012m7 |
     |------------------------------------------------------------------|
145. | FSUSA003T7   A. Douglas Rao   01feb2010   20jul2012       2010m2 |
174. | FSUSA003T7   A. Douglas Rao   01feb2010   20jul2012       2012m7 |
     +------------------------------------------------------------------+

[Ulrica He]

These codes works!
Thanks a lot William.


What is the difference between bysort managername fundid and bysort managername (fundid)?

[William Lisowski]

What is the difference between bysort managername fundid and bysort managername (fundid)?

In both cases, the dataset is first sorted by managername and fundid.

Without the parentheses, the replace command is run on each combination of managername and fundid.

With the parentheses, the replace command is run on each managername across all values of fundid. In other words, fundid is used for sorting, but not for forming by-groups.

We often use this where the parenthesized variable represents a sequence number or date, so that

bysort person (sequence): generate diff = x[_n]-x[_n-1]

will then calculate the difference of x between successive values of sequence, separately for each person, so that the difference on the first observation for the second person won't be based on the last observation of the first person.

[Ulrica He]

Thanks a lot for the clear explanation.