Matching code

[Yousef Kaddoura]

Hello again

I have a problem where I'm trying to match daily data to annual data. I have to sets of data and 1 is annual and the other is in monthly and I'm trying to merge both. I tried to use all the merge functions possible and I couldn't come to a conclusion...

My monthly data:

firm datadate cshoc prccd market ret vol
15496 02jan1986 1131429 2010 2.27e+09 .0286097
15496 03jan1986 1131429 2150 2.43e+09 .0696517 .0286097
15496 06jan1986 1131429 2200 2.49e+09 .0232558 .0286097
15496 07jan1986 1131429 2090 2.36e+09 -.05 .0286097
15496 08jan1986 1131429 2350 2.66e+09 .1244019 .0286097
15496 09jan1986 1131429 2448 2.77e+09 .0417021 .0286097
15496 10jan1986 1131429 2500 2.83e+09 .0212418 .0286097
15496 13jan1986 1131429 2620 2.96e+09 .048 .0286097
15496 14jan1986 1131429 2650 3.00e+09 .0114504 .0286097
.
.
.
.
firm datadate cshoc prccd market ret vol
15496 28apr2014 53679994 112.65 6.05e+09 .0013333 .0030562
15496 29apr2014 53679994 112.8 6.06e+09 .0013316 .0030562
15496 30apr2014 53679994 112.65 6.05e+09 -.0013298 .0030562
15496 01may2014 53679994 112.65 6.05e+09 0 .0030562
15496 02may2014 53679994 112.65 6.05e+09 0 .0030562
15496 05may2014 53679994 113.85 6.11e+09 .0106525 .0030562
15496 06may2014 53679994 113.4 6.09e+09 -.0039526 .0030562
15496 07may2014 53679994 112.7 6.05e+09 -.0061728 .0030562
15496 08may2014 53679994 113.25 6.08e+09 .0048802 .0030562
15496 06jun2014 .0030562


My annual data:

firm indfmt datafmt consol popsrc fyear datadate
15496 FS HIST_STD C I 1988 31dec1988
15496 FS HIST_STD C I 1989 31dec1989
15496 FS HIST_STD C I 1990 31dec1990
15496 FS HIST_STD C I 1991 31dec1991
15496 FS HIST_STD C I 1992 31dec1992
15496 FS HIST_STD C I 1993 31dec1993
15496 FS HIST_STD C I 1994 31dec1994
15496 FS HIST_STD C I 1995 31dec1995
15496 FS HIST_STD C I 1996 31dec1996
15496 FS HIST_STD C I 1997 31dec1997
15496 FS HIST_STD C I 1998 31dec1998
15496 FS HIST_STD C I 1999 31dec1999
15496 FS HIST_STD C I 2000 31dec2000
15496 FS HIST_STD C I 2001 31dec2001
15496 FS HIST_STD C I 2002 31dec2002
15496 FS HIST_STD C I 2003 31dec2003
15496 FS HIST_STD C I 2004 31dec2004
15496 FS HIST_STD C I 2005 31dec2005
15496 FS HIST_STD C I 2006 31dec2006
15496 FS HIST_STD C I 2007 31dec2007
15496 FS HIST_STD C I 2008 31dec2008
15496 FS HIST_STD C I 2009 31dec2009
15496 FS HIST_STD C I 2010 31dec2010
15496 FS HIST_STD C I 2011 31dec2011
15496 FS HIST_STD C I 2012 31dec2012
15496 FS HIST_STD C I 2013 31dec2013


What I want is try to match each year in the annual data with the end of each year in the monthly data. what I really want is the market and vol (volatility) to be matched in the annual data!


Thank you in advance!

Regards,

[Clyde Schechter]

I'm not clear on what you want. If you want to retain all the monthly return data and pair these observations up with the corresponding annual data for the firm and year it is this:

use monthly_data, clear
gen fyear = year(datadate)
merge m:1 firm fyear using annual_data

If you want to extract from the return data a single record with the final returns for the year, and then match those with the corresponding annual data for the firm and year you would do this:

use monthly_data, clear
gen fyear = year(datadate)
by firm fyear (datadate), sort: keep if _n == _N
merge 1:1 firm fyear using annual_data

If it's neither of these, then rather than explaining, please post an example of what the result you are looking for would look like.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Yousef Kaddoura]

Thank you Clyde! It worked,
What I needed was actually option number 2. Ill keep in mind your advice when asking my next question! but if there is any toturial on how i should ask my questions and you could send it here, I would appreciate it!

Regards,

[Clyde Schechter]

Just read the forum FAQ. (There is a link to them in the upper left part of this page, just under the Statalist banner.) It has excellent advice on how to ask questions here. It's the best tutorial there is.