Merge data help: 2 files, panel data (trade data) with bilateral country pairs (CEPII, Gravity, DESTA)

[Abdul Ghafoor Khan]

I want to merge two data files. In file 1, I have bilateral trade data for 30 years with the exporter, importer id along with the year and other variables. In file 2, I have the data on the characteristics of the trade agreements signed between two or more countries. Based on country IDs from file 1, I have made bilateral pairs (country A and B) based on agreement signatory members along with the year of agreement in the next column. Country A and B (Both A and B can either be an exporter or importer) along with the year of the trade agreement. Now I want to merge file 2 into file 1 in such a way that each agreement characteristic (4 variables) information is reflected in front of their respective bilateral country pair (Exporter, Importer, year) from the year of the agreement till last time of the agreement (e.g., 1995 - 2003) because a revised agreement may have been signed between countries and new characteristics values need to be reflected for the next period between these countries (e.g. 2004-2014). Second in file 1 exporter and importer country is defined but in file 2, both Country A and B can be an exporter or importer (both way) because agreement works both way so how to reflect this while moving each agreement characteristics variables to file 1. Doing this manually for more than 100000 observations is a very time-consuming job. Is there any simple solution for this in Stata. I have attached both file sample excel file 1 and file 2 for your review.

[Clyde Schechter]

Please read the Forum FAQ for excellent advice about how to maximize your chances of getting timely and helpful responses on Statalist. Among the important things you will learn there are:

1. Attachments are discouraged. Some Forum members will not download attachments from people they do not know.
2. The helpful way to show example data is with the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- 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.

To these general principles, I will add some specifics applicable to your situation. First, whatever solution in Stata is found, it will have to apply to Stata data sets, not spreadsheets. So, if you have not yet imported your data into Stata, it is premature to even ask for this help. If you have, then posting example data (with -dataex-, as already noted) from the Stata data sets is the way to go. I also note that although some problems can be solved without example data, answers about how to combine data from two files depend heavily on details of the data and metadata from those files--these question can almost never be answered without example data to work from.