merge on most recent fiscal year end

[Jae Li]

Hi Statalist,

I am trying to merge an annual dataset with a quarterly dataset based on most recent fiscal year end (fym) observations for each firm. I am using the following code from another post:

joinby permno fyear using "inv.dta", unmatched(master)
drop if (fym > ym) & !missing(fym)
bysort permno fyear (fym): keep if (_n == _N)

However, there is an issue about the code. Since the fiscal year end observations (fym) are mostly less than one year than calendar year observations (ym), so my question is how to adjust the above code to merge data on most recent fiscal year end (fym) when the fiscal year end date is equal to or above than calendar time? How to make the code work for both occasions? Many thanks for your help in advance!

For example:

quarter	fyear	permno	gvkey	tmi1	ym	wtio	und_inv	fym
1987q1	1986	10003	16950	0	1987m3	0.000885		1986m5
1987q2	1986	10003	16950	0	1987m6	0.000351		1986m5
1987q3	1986	10003	16950	0	1987m9	0.00029		1986m5
1987q4	1986	10003	16950	0	1987m12	0.00031		1986m5
1994q1	1994	10003	16950	0	1994m3	2.76E-07		1994m12
1994q2	1994	10003	16950	0	1994m6	5.20E-06		1994m12
1994q3	1994	10003	16950	0	1994m9	4.26E-06		1994m12
1994q4	1994	10003	16950	0	1994m12	3.09E-07		1994m12

Here are my data:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input float quarter double(fyear permno) long gvkey double tmi1 float ym double wtio float(fym und_inv)
104 1986 10010 7783 0 314  5.853308262824048e-08 321         .
107 1986 10010 7783 0 323  9.090024485579905e-08 321         .
111 1987 10010 7783 0 335  7.296299902402809e-09 333         .
112 1988 10010 7783 0 338   9.04250789633261e-09 345 .04130736
113 1988 10010 7783 0 341 1.5004858307195105e-08 345 .04130736
114 1988 10010 7783 0 344 1.4930220832011817e-08 345 .04130736
115 1988 10010 7783 0 347  5.785719825588936e-08 345 .04130736
116 1989 10010 7783 0 350   9.15401001984344e-08 357 .04325666
117 1989 10010 7783 0 353 1.7963004052492917e-07 357 .04325666
118 1989 10010 7783 0 356  1.960968181659658e-07 357 .04325666
119 1989 10010 7783 0 359 2.6114797782110516e-07 357 .04325666
120 1990 10010 7783 0 362   8.15230075645831e-07 369         .
121 1990 10010 7783 0 365  .00007290186864352296 369         .
122 1990 10010 7783 0 368  .00006923077180023174 369         .
123 1990 10010 7783 0 371  .00007587758733202803 369         .
124 1991 10010 7783 0 374   .0003396585019387856 381         .
125 1991 10010 7783 0 377   .0005351464599495044 381         .
126 1991 10010 7783 0 380  .00010967411598148718 381         .
127 1991 10010 7783 0 383  .00004216177591842087 381         .
128 1992 10010 7783 0 386  .00005499661859774912 393         .
129 1992 10010 7783 0 389   .0000131393498655912 393         .
130 1992 10010 7783 0 392  .00001537787665207034 393         .
131 1992 10010 7783 0 395 .000020286660519556732 393         .
132 1993 10010 7783 0 398 .000010675809323527865 405  .2184157
133 1993 10010 7783 0 401  5.964022509448425e-06 405  .2184157
134 1993 10010 7783 0 404   7.30353444262509e-06 405  .2184157
135 1993 10010 7783 0 407  3.280045935777243e-06 405  .2184157
136 1994 10010 7783 0 410   .0000261188654840183 417         .
137 1994 10010 7783 0 413  .00006779810529794544 417         .
138 1994 10010 7783 0 416  .00003138719411095604 417         .
139 1994 10010 7783 0 419  .00001708528489489326 417         .
end
format %tq quarter
format %tm ym
format %tm fym
label values gvkey gvkey1
label def gvkey1 7783 "012622", modify

------------------ copy up to and including the previous line ------------------

Listed 31 out of 585367 observations

.