Replacing missing variable with other observations that satisfy certain conditions

[Gia Cat Luong]

Hi all,
I am working on a cross-country dataset. The dataset is created when I merge the bilateral trade data with the country characteristics (such as GDP and bilateral trade agreements). When I merge I use the exporter ISO code, the importer ISO code and time as the identifiers. My master data is the trade data and my using data is the characteristics data. In the using data I rename the country of origin the exporter and the country of destination the importer. For instance, in the merged data I will have the trade value exported by country A to country B, the GDP of country A and of country B and whether A and B have a trade agreement. However, what the data misses is when B exports to A I don't have the characteristics because the using data already considers A as the origin and B as the destination (due to the way I rename the variables in the using data). I cannot merge the data one more time with A being the destination and B as the origin because of duplication (the free trade agreement variable will appear twice). I was then thinking of replacing the missing observation when B exports to A by the value of the observation when A exports to B thanks to symmetry. I checked on the forum and what I can find is I need know the ID of the observation (i.e. replace GDP = GDP[6]). In my case, I only know the observation satisfies certain conditions: replace GDP = GDP of the observation whose Exporter=Importer and Importer=Exporter.

To illustrate my point, here is an example:

Before:

Exporter Importer Exporter_GDP Importer_GDP Trade value
A B 100 200 50
B A . . 70

After

Exporter Importer Exporter_GDP Importer_GDP Trade value
A B 100 200 50
B A 200 100 70

Could you please give me some suggestions?

Many thanks,

Gia Cat Luong

[Joro Kolev]

Try to provide an example dataset with -dataex- so that we have something to work with.

[LEILA BEN AOUN]

Here is one solution ...
I create 2 separate files from your table and with a merge it helps ...

My example dataset is

country1	country2	import	export	trade
a	b	100	200	50
b	a			70
c	d	10	20	30
d	c		30	40
e	f			60
f	e	12	33	44

cd f:\
use "test.dta" , clear
n: list , sep(99) noobs


* create the couples
gen couple1 = country1 + country2
gen couple2 = country2 + country1
save all.dta , replace

* mini table 1
use all ,clear
ren * couple1_*
drop *couple2
ren *couple1 couple
save couple1 , replace

* mini table2
use all ,clear
ren * couple2_*
drop *couple1
ren *couple2 couple
save couple2 , replace

use couple1 , clear
merge  1:1 couple using couple2
drop _merge

* first detect "missing cases" (Both cases are missing IMPORT & EXPORT)
gen tag_tofillin =  mi(couple1_import) & mi(couple1_export)

replace couple1_import=couple2_export if  tag_tofillin
replace couple1_export=couple2_import if  tag_tofillin
drop couple2_* couple
ren couple1_* *
n: list , sep(99) noobs

The results is

country1	country2	import	export	trade
a	b	100	200	50
b	a	200	100	70
c	d	10	20	30
d	c		30	40
e	f	33	12	60
f	e	12	33	44

I hope this will help you

[Gia Cat Luong]

Thank you Leila. I need to do some tweaking but your idea works for me.