Merging panel data with time specific indicators

[Ron Philip]

Hello,

i have two datasets that should be merged.
The first dataset is panel data consisting of a unique firm identifier (ID) a date variable (fiscal year end, e.g. 31march2010) and a panel variable, year, 2009 (for 31march2010, respectively). Furthermore each row consists of a lot of variables about all firms in the dataset and an second identifier name. This second identifier enables me to merge with different datasets which use the same identfier. The problem with this identifier is that it changes when firms change names and is reused after time.


The second dataset (linking table) consists of various date variables were first time name was distributed to a company and last day name was used. The next row then shows the new name for the next time period This dataset also consits of a unique firm identifier firmid, that is used by my third dataset.

Now i search for a code that merges firmid to ID based on the variable name, if date lies in the interval of first and last day a firm using this identifier (name).

panel Data

ID	date	year	name
123	31march2009	2008	ZZAGFR
123	31march2010	2009	ZZAGFR
123	31march2011	2010	REDFTZ

Linking table

date	name	first day	last day	firm id
01may2005	ZZAGFR	01may2005	25april2009	5558877
26april2009	REDFTZ	26april2009	.	5558877
and so on for thousands of firms

The required commands should yield the following desired dataset.

ID	date	year	name	firm id
123	31march2009	2008	ZZAGFR	5558877
123	31march2010	2009	ZZAGFR	5558877
123	31march2011	2010	REDFTZ	5558877

With this dataset i am able to merge with third dataset, which is my overall goal.

I would be really happy if someone is able to help me.

[Amin Sofla]

You might want to use rangejoin. See its help for more details.

ssc install rangestat
ssc install rangejoin
help rangejoin