merging datasets

[Sandra Rhouma]

Dear readers,

I have two different data sets that I would like to merge in a specific way. The first data set is a panel containing countries years and debt in the following way:

Country Year DEBT

USA 2000 6000
USA 2001 5231
USA 2002 5981
UK 2000 1547
UK 2001 4897
UK 2002 5465
France 2000 5468
France 2001 5476
France 2002 5479

The second data set is also a panel but containing both countries and their trade partners as well as the value of imports:

Country Partern Year Tradeweights

USA UK 2000 0.6
USA UK 2001 0.76
USA UK 2002 0.62
USA France 2000 0.3
USA France 2001 0.35
USA France 2002 0.4
UK France 2000 0.6
UK France 2001 0.68
UK France 2002 0.64
UK USA 2000 0.54
UK USA 2001 0.36
UK USA 2002 0.45
France UK 2000 0.26
France UK 2001 0.36
France UK 2002 0.45
France USA 2000 0.56
France USA 2001 0.26
France USA 2002 0.39

I would like to calculate for each country and each year the weighted average of the debt of their trade partners. For example, the weighted debt of USA in 2000=debt of UK*weight of UK+debt of France*weight of France. However, when merging the two data sets I have the following:

Country Partner Year Tradeweights DEBT


USA UK 2000 0.6 6000
USA UK 2001 0.76 5231
USA UK 2002 0.62 5981
USA France 2000 0.3 6000
USA France 2001 0.35 5231
USA France 2002 0.4 5981
UK France 2000 0.6 1547
UK France 2001 0.68 4897
UK France 2002 0.64 5465
UK USA 2000 0.54 1547
UK USA 2001 0.36 4897
UK USA 2002 0.45 5465
France UK 2000 0.26 5468
France UK 2001 0.36 5476
France UK 2002 0.45 5479
France USA 2000 0.56 5468
France USA 2001 0.26 5476
France USA 2002 0.39 5468

So the problem is that the DEBT column is the debt of the country and not the partner, so I can't see how I can implement a subroutine to calculate weighted debt. I tried to merge the data set so that DEBT would match with Partner and not Country, but Partner is not common to both data sets. I also tried to create a column which identifies the debt of the partners, without success.

Any help/advice would be really appreciated.

Regards,

[Carlo Lazzaro]

Triplicate post?

[Robert Picard]

To merge DEPT for the partner country, you simply need to temporarily change the variable names to perform the desired merge. Something like:

clear
input str10 Country Year DEBT
USA 2000 6000 
USA 2001 5231 
USA 2002 5981
UK 2000 1547 
UK 2001 4897
UK 2002 5465
France 2000 5468
France 2001 5476 
France 2002 5479 
end
isid Country Year, sort
tempfile debt
save "`debt'"

clear
input str10 Country str10 Partern Year Tradeweights
USA UK 2000 0.6 
USA UK 2001 0.76
USA UK 2002 0.62
USA France 2000 0.3
USA France 2001 0.35 
USA France 2002 0.4
UK France 2000 0.6 
UK France 2001 0.68 
UK France 2002 0.64
UK USA 2000 0.54 
UK USA 2001 0.36
UK USA 2002 0.45
France UK 2000 0.26
France UK 2001 0.36 
France UK 2002 0.45 
France USA 2000 0.56
France USA 2001 0.26
France USA 2002 0.39
end 
isid Country Year Partern, sort
list

* temporarily change variable names to merge Partern with its debt
rename Country Country0
rename Partern Country
merge m:1 Country Year using "`debt'"

* restore variable names
rename Country Partern
rename Country0 Country 
sort Country Year Partern
list