Merging correctly

[Sabine Swennenhuis]

Hi,

I have a list of data from the compustat and the execucomp databases already merged together for the years 2001 to 2006. This data seems to be correct..

I have another dataset with patent data from the NBER database, merged with a patent file with the right assignee. This one contains all the patents applied for by each company (GVKEY) and for the year in which the companies applied for this patent (ayear) and npat, the number of patents for each pdpass-year.

Now when I merge these two databases (I seem to only be able to use joinby instead of a merge), it gives me all the financial data for every fiscal year multiple times (for every ayear). Therefore I'm not able to correctly create mean variables etc.

This is what it looks like:

Is there another way to merge them, so I will only keep one observation per fiscal year, but I will be able to see in which year they applied for a patent?

Thanks in advance.
Sabine

[Clyde Schechter]

Because you have shown screenshots of our data, which the FAQ explicitly says NOT to do, it is difficult to be sure what is going on here. But as best I can tell, the problem is that one of your data sets has a variable ayear for year, and in the other data set it is called fyear. That is the reason you can't get -merge- to work: the merge key variable names are not consistent across data sets.

I think if you -rename- one of those variables to match the other, you will be able to -merge 1:1 gvkey year_variable- (substitute the actual name of the year variable that you choose) and everything should go smoothly from there.

Please do read the FAQ about how to post example data using the -dataex- command.