Merge: Merging datasets with observations corresponding to two different observations in the other dataset

[Jala Youssef]

Hi everyone,

I have a dataset with sectoral trade values by country. I would like to merge this dataset with a small dataset that I am creating manually. Please find below an example of the original dataset:

country	year	code	sector	tradevalue
EGY	2002	SD	Travel
EGY	2002	SG	Financial services
EGY	2002	SJ	Other business services
JOR	2002	SD	Travel
JOR	2002	SG	Financial services
JOR	2002	SJ	Other business services

My question here is that I would like to merge some observations twice with two different values in the other dataset (that I am creating manually). Please find below an example:

code	sector	isiccode	isiccodedescription
SD	Travel	55	Hotels and restaurants
SD	Travel	63	Supporting and auxiliary transport activities; activities of travel agencies
SG	Financial services	65	Financial intermediation, except insurance and pension funding
SJ	Other business services	74	Other business activities

Here for example I would like to merge the observation "SD" in the original dataset twice with two observations in the manually created dataset: once with isiccode 55 and once with isiccode 63.

I would be grateful if you could kindly advise whether I should repeat the observations that have to be merged twice. If that is the case, how would you recommend to do it?

I am sorry in advance if this has been asked before and I couldn't locate the answer.

Many thanks in advance.

Jala Youssef

[Clyde Schechter]

From what you have shown, it appears that code is the only common variable that might link the two data sets. So I take it you want to pair both SD observations in the second data set with all observations in the first data set that have code = SD, regardless of year or country. If that is what you want to do, it is just done with -joinby-, not -merge-.

use first_data_set, clear
joinby code using second_data_set

Do read -help joinby- before doing this so that you can add any appropriate options to the command, such us those which enable you to control what is done with observations in the two data sets that find no match in the other.

If you need more concrete help with this, when posting back, please use the -dataex- command to show the example data, as the table you have shown in your post are not capable of including metadata that is sometimes critical to resolving code problems. If you are running version 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.

[Jala Youssef]

Dear Clyde Schechter,

Many thanks for the very useful reply. This is much appreciated at my end.

Well noted for the "dataex" command. I will make sure to use it in my future posts.

Jala