merge database and expanding observations

[Andres Sanabria]

Dear Statalist,

I've learnt lots reading through different posts and now it is time for me to ask for guidance.

I have to data sets that I need to merge and creating new observations for my first database based on the second one.

My first database contains trade info (exports and imports) per commodity among countries thought different years. The other has the trade among these countries with an extra classification of commodities (some commodities split in two) and the weights for the exp and imp of each trade interaction.

So what I want is to merge the data set and have a new data set where each year expands by the times that the times that the commodity expands in the second data set. Generally in the second dataset the commodoty (Code 1) is divided by two other commodities (code 2) but there are also case where it divides by three or it just change the name by other commodity code.

At the end, the important is to have the list with Code 2 commodities per year.

Database 1							Database 2
country	partner	year	Code1	Exp	Imp		country	partner	Code1	Code2	WeightsExp	Imp
8	1	2002	380210	15140	2881		8	1	380210	880215	3%	60%
8	1	2003	380210	8822	155170		8	1	380210	258215	97%	40%
8	1	2004	380210	15140	191241		8	2	380210	880215	8%	65%
8	1	2005	380210	10405	238101		8	2	380210	258215	92%	35%
8	1	2006	380210	32026	8397		8	3	380210	880215	13%	70%
8	1	2007	380210	20764	88964		8	3	380210	258215	87%	30%
8	1	2008	380210	.	108962		8	4	380210	880215	18%	75%
8	1	2009	380210	32028	139283		8	4	380210	258215	82%	25%
8	1	2002	540289	83.48556	364.1957		8	1	540289	998215	23%	80%
8	1	2003	540289	27.78345	466.4429		8	1	540289	458215	77%	20%
8	1	2004	540289	444.2658	450.8809
8	1	2005	540289	780.0317	384.1828
8	1	2006	540289	331.0234	525.5911
8	1	2007	540289	190.5223	753.1146
8	1	2008	540289	.	458.0845
8	1	2009	540289	422.9982	110.9116
8	2	2002	380210	710.226	638.0749
8	2	2003	380210	95.60632	80.69293
8	2	2004	380210	495.5146	382.1977
8	2	2005	380210	273.3138	669.9371
8	2	2006	380210	306.1737	386.7093
8	2	2007	380210	64.98047	534.9051
8	2	2008	380210	.	623.8192
8	2	2009	380210	391.7373	799.9001
I want a dataset like:
Database 1&2
country	partner	year	Code1	Code2	Exp	Imp	WeightsExp	Imp
8	1	2002	380210	880215	15140	2881	3%	60%
8	1	2002	380210	258215	15140	2882	97%	40%
8	1	2003	380210	880215	8822	155170	3%	60%
8	1	2003	380210	258215	8822	155170	97%	40%
8	1	2004	380210	880215	15140	191241	3%	60%
8	1	2004	380210	258215	15140	191241	97%	40%
8	1	2005	380210	880215	10405	238101	3%	60%
8	1	2005	380210	258215	10405	238101	97%	40%
8	2	2002	380210	258215	32026	8397	8%	65%
8	2	2002	380210	880215	20764	88964	92%	35%
8	2	2003	380210	258215	.	108962	8%	65%
8	2	2003	380210	880215	32028	139283	92%	35%
		(..)
8	1	2002	540289	258215	201.9247	751.0696	8%	65%
8	1	2002	540289	880215	527.3729	372.2685	92%	35%

(..) means that the list for country 8 exporting or impoting commodity "380210"continues with other coutnries (the list goes until 70 countries)

Thank you in advance!!

Andres

[Clyde Schechter]

If I understand correctly, what you want is not a -merge-. What you want is a join.

use dataset1
joinby country Code1 using dataset2

[Andres Sanabria]

Dear Clyde,

It was very simple!

thank you