Creating fiscal month given fiscal year end month

[Alberto Alvarez]

Dear Stata Users,

Please, help me with the following issue. I have a data uploaded below. What I need is to create a "year_m"==year(date) and "month_m" after "fyrc"==month the very first time withing a given firm ("gvkey"). After twelve months (month(date)) the year should increase by one. For example, "year_m" ==1997 from 9th observations until 20th and "month_m"=1 in the 9th obs and increases to 12 at 20th obs. From 21st "year_m" should change into 1998 and "month_m" should start from 1, etc. Sometimes there are might be gaps as between observation 69 and 70. In this case "year_m"==2015 from 63 obs till 73 and "month_m"=1 in the 63rd obs, increases by one until 69th, at 70th it increases by 2 (since there is a missing observation) "month_m" in the 69th obs = 7 and in 70th=9 , then continues increasing until 12.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double fyrc float(month year) long date
"001004"  5 10 1996 13453
"001004"  5 11 1996 13482
"001004"  5 12 1996 13514
"001004"  5  1 1997 13545
"001004"  5  2 1997 13573
"001004"  5  3 1997 13604
"001004"  5  4 1997 13634
"001004"  5  5 1997 13664
"001004"  5  6 1997 13695
"001004"  5  7 1997 13726
"001004"  5  8 1997 13755
"001004"  5  9 1997 13787
"001004"  5 10 1997 13818
"001004"  5 11 1997 13846
"001004"  5 12 1997 13879
"001004"  5  1 1998 13909
"001004"  5  2 1998 13937
"001004"  5  3 1998 13969
"001004"  5  4 1998 13999
"001004"  5  5 1998 14028
"003897" 12  1 2005 16467
"003897" 12  2 2005 16495
"003897" 12  3 2005 16526
"003897" 12  4 2005 16555
"003897" 12  5 2005 16587
"003897" 12  6 2005 16617
"003897" 12  7 2005 16646
"003897" 12  8 2005 16679
"003897" 12  9 2005 16709
"003897" 12 10 2005 16740
"003897" 12 11 2005 16770
"003897" 12 12 2005 16800
"003897" 12  1 2006 16832
"003897" 12  2 2006 16860
"003897" 12  3 2006 16891
"003897" 12  4 2006 16919
"003897" 12  5 2006 16952
"003897" 12  6 2006 16982
"003897" 12  7 2006 17013
"003897" 12  8 2006 17044
"003897" 12  9 2006 17073
"003897" 12 10 2006 17105
"003897" 12 11 2006 17135
"003897" 12 12 2006 17164
"005492"  6  1 2014 19754
"005492"  6  2 2014 19782
"005492"  6  3 2014 19813
"005492"  6  4 2014 19843
"005492"  6  5 2014 19873
"005492"  6  6 2014 19904
"005492"  6  7 2014 19935
"005492"  6  8 2014 19964
"005492"  6  9 2014 19996
"005492"  6 10 2014 20027
"005492"  6 11 2014 20055
"005492"  6 12 2014 20088
"005492"  6  1 2015 20118
"005492"  6  2 2015 20146
"005492"  6  3 2015 20178
"005492"  6  4 2015 20208
"005492"  6  5 2015 20237
"005492"  6  6 2015 20269
"005492"  6  7 2015 20300
"005492"  6  8 2015 20331
"005492"  6  9 2015 20361
"005492"  6 10 2015 20391
"005492"  6 11 2015 20422
"005492"  6 12 2015 20453
"005492"  6  1 2016 20482
"005492"  6  3 2016 20544
"005492"  6  4 2016 20573
"005492"  6  5 2016 20605
"005492"  6  6 2016 20635
"005492"  6  7 2016 20664
"005492"  6  8 2016 20697
"005492"  6  9 2016 20727
"005492"  6 10 2016 20758
"005492"  6 11 2016 20788
"005492"  6 12 2016 20818
end
format %d date

[Andrew Musau]

gen year_m = cond(month<= fyrc, year-1, year)
bys gvkey year (month):gen month_m= cond( month> fyrc, month- fyrc, 12- (fyrc-month))

Result:

. l, clean

        gvkey   fyrc   month   year        date   year_m   month_m  
  1.   001004      5      10   1996   31oct1996     1996         5  
  2.   001004      5      11   1996   29nov1996     1996         6  
  3.   001004      5      12   1996   31dec1996     1996         7  
  4.   001004      5       1   1997   31jan1997     1996         8  
  5.   001004      5       2   1997   28feb1997     1996         9  
  6.   001004      5       3   1997   31mar1997     1996        10  
  7.   001004      5       4   1997   30apr1997     1996        11  
  8.   001004      5       5   1997   30may1997     1996        12  
  9.   001004      5       6   1997   30jun1997     1997         1  
 10.   001004      5       7   1997   31jul1997     1997         2  
 11.   001004      5       8   1997   29aug1997     1997         3  
 12.   001004      5       9   1997   30sep1997     1997         4  
 13.   001004      5      10   1997   31oct1997     1997         5  
 14.   001004      5      11   1997   28nov1997     1997         6  
 15.   001004      5      12   1997   31dec1997     1997         7  
 16.   001004      5       1   1998   30jan1998     1997         8  
 17.   001004      5       2   1998   27feb1998     1997         9  
 18.   001004      5       3   1998   31mar1998     1997        10  
 19.   001004      5       4   1998   30apr1998     1997        11  
 20.   001004      5       5   1998   29may1998     1997        12  
 21.   003897     12       1   2005   31jan2005     2004         1  
 22.   003897     12       2   2005   28feb2005     2004         2  
 23.   003897     12       3   2005   31mar2005     2004         3  
 24.   003897     12       4   2005   29apr2005     2004         4  
 25.   003897     12       5   2005   31may2005     2004         5  
 26.   003897     12       6   2005   30jun2005     2004         6  
 27.   003897     12       7   2005   29jul2005     2004         7  
 28.   003897     12       8   2005   31aug2005     2004         8  
 29.   003897     12       9   2005   30sep2005     2004         9  
 30.   003897     12      10   2005   31oct2005     2004        10  
 31.   003897     12      11   2005   30nov2005     2004        11  
 32.   003897     12      12   2005   30dec2005     2004        12  
 33.   003897     12       1   2006   31jan2006     2005         1  
 34.   003897     12       2   2006   28feb2006     2005         2  
 35.   003897     12       3   2006   31mar2006     2005         3  
 36.   003897     12       4   2006   28apr2006     2005         4  
 37.   003897     12       5   2006   31may2006     2005         5  
 38.   003897     12       6   2006   30jun2006     2005         6  
 39.   003897     12       7   2006   31jul2006     2005         7  
 40.   003897     12       8   2006   31aug2006     2005         8  
 41.   003897     12       9   2006   29sep2006     2005         9  
 42.   003897     12      10   2006   31oct2006     2005        10  
 43.   003897     12      11   2006   30nov2006     2005        11  
 44.   003897     12      12   2006   29dec2006     2005        12  
 45.   005492      6       1   2014   31jan2014     2013         7  
 46.   005492      6       2   2014   28feb2014     2013         8  
 47.   005492      6       3   2014   31mar2014     2013         9  
 48.   005492      6       4   2014   30apr2014     2013        10  
 49.   005492      6       5   2014   30may2014     2013        11  
 50.   005492      6       6   2014   30jun2014     2013        12  
 51.   005492      6       7   2014   31jul2014     2014         1  
 52.   005492      6       8   2014   29aug2014     2014         2  
 53.   005492      6       9   2014   30sep2014     2014         3  
 54.   005492      6      10   2014   31oct2014     2014         4  
 55.   005492      6      11   2014   28nov2014     2014         5  
 56.   005492      6      12   2014   31dec2014     2014         6  
 57.   005492      6       1   2015   30jan2015     2014         7  
 58.   005492      6       2   2015   27feb2015     2014         8  
 59.   005492      6       3   2015   31mar2015     2014         9  
 60.   005492      6       4   2015   30apr2015     2014        10  
 61.   005492      6       5   2015   29may2015     2014        11  
 62.   005492      6       6   2015   30jun2015     2014        12  
 63.   005492      6       7   2015   31jul2015     2015         1  
 64.   005492      6       8   2015   31aug2015     2015         2  
 65.   005492      6       9   2015   30sep2015     2015         3  
 66.   005492      6      10   2015   30oct2015     2015         4  
 67.   005492      6      11   2015   30nov2015     2015         5  
 68.   005492      6      12   2015   31dec2015     2015         6  
 69.   005492      6       1   2016   29jan2016     2015         7  
 70.   005492      6       3   2016   31mar2016     2015         9  
 71.   005492      6       4   2016   29apr2016     2015        10  
 72.   005492      6       5   2016   31may2016     2015        11  
 73.   005492      6       6   2016   30jun2016     2015        12  
 74.   005492      6       7   2016   29jul2016     2016         1  
 75.   005492      6       8   2016   31aug2016     2016         2  
 76.   005492      6       9   2016   30sep2016     2016         3  
 77.   005492      6      10   2016   31oct2016     2016         4  
 78.   005492      6      11   2016   30nov2016     2016         5  
 79.   005492      6      12   2016   30dec2016     2016         6

[Nick Cox]

See also Section 4 on non-calendar years within https://www.stata-journal.com/sjpdf....iclenum=st0394

[Alberto Alvarez]

Thank you!