st: Merging annual data and monthly data few months after year end

["Abdalla, Ahmed" <ahmed.abdalla@kcl.ac.uk>]

Dear Statalist
I have two databases:
a) ccm.dta that includes "annual" accounting data at fiscal year end for a set of firms over 20 years (however, unbalanced). 

Permno (firm identifier)   datadate                          yr             mth               x1          x2

10001                                    30june1988                  1988            6                 
10001                                    30june1989                  1989            6
10001                                    31december1990        1990            12
10002                                    30april1988                  1988             4
10002                                    30april1989                  1989             4           
10002                                    31may                           1990            5

Note that a company may change its fiscal year end. A company that reports accounting data at the end of June this year may report data at December in the next year (or sometimes at the same year).

b) crsp.dta that includes "monthly"stock prices for the same set of firms over the same period (however, unbalanced)

Permno (firm identifier)      datadate                                  yr       mth     x3(stockprice)
10001                                    29january1988                      1988       1
10001                                    29february1988                    1988       2
10001                                    31march1988                        1988       3
10001                                    29april1988                           1988       4
10001                                    31may1988                            1988       5
10001                                    30june1988                           1988       6
10001                                    31july1988                             1988      7
....                                           ....
....                                           ...31dec1988                         1988      12

10001                                    29january1989                      1989     1
10001                                    29february1989                     1989     2
10001                                    31march1989                        1989      3
10001                                    29april1989                           1989     4
10001                                    31may1989                            1989     5
10001                                    30june1989                            1989    6
10001                                    31july1989                             1989     7
....                                           ....
....                                           ...31dec1989                          1989    8

10002                                    29january1988                       1988    1                 
10002                                    29february1988                     1988     2
10002                                   31march1988                          1988     3
10002                                    29april1988                            1988     4
10002                                    31may1988                             1988    5
10002                                    30june1988                             1988    6
10002                                    31july1988                               1988   7
....                                           ....
....                                           ...31dec1989                            1988 12


I want to merge both data sets and link annual accounting data in ccm.dta to stock prices reported at the end of fiscal year and three months after the end of the fiscal year. Here is the code I used:

use ccm.dta
duplicates drop
format datadate %d
  * yr and mth were not reported in the original data in both datasets, however I wrote them in my example above*
gen yr=year(datadate) 
gen mth=month(datadate)
drop if permno==.
sort permno yr mth
save ccm_2.dta

use crsp.dta,clear
duplicates drop
format date %d
gen yr=year(date)
gen mth=month(date)
drop if permno==.
sort permno yr mth
merge 1:1 permno yr mth using ccm_2.dta
egen firmid= group(permno)
egen timeid=group(yr mth)
tsset firmid timeid
gen prc3=f3.prc
keep if _merge==3


The code didn't work and gives error messages that I couldn't fix and especially when I "tsset" my panel.

Would you please suggest a proper code to merge these two datasets and link x1 x2 (annual data) to x3 (stock prices) at fiscal year end and three months after fiscal year end ?

Many thanks in advance
Ahmed





  
 
 
 
     
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/