Merge datasets with different structures

[tom berk]

Hi All,


I want to merge some datasets with my main data set but the structure of the datasets differ, which make merging impossible in the current structure.

My main data looks like:

* Example generated by -dataex-. For more info, type help dataex
clear
input str18 county str3 company int year
"1" "a" 2000
"1" "a" 2001
"1" "a" 2002
"1" "a" 2003
"1" "b" 1981
"1" "b" 1982
"1" "b" 1983
"2" "c" 1991
"2" "c" 1992
"2" "c" 1993
"2" "c" 1994
"2" "c" 1995
"2" "c" 1996
""  ""     .
""  ""     .
""  ""     .
end

The data sets that I want to merge are in table form and look like:

* Example generated by -dataex-. For more info, type help dataex
clear
input str18 county double y1980 float(y1981 y1982 y1983 y1984)
"1" 31.15 31.35 31.61 31.84 32.07
"2" 33.86  34.2 34.59 34.93 35.25
"3" 33.91 34.03  34.2 34.33 34.47
"4" 33.23 33.34 33.51 33.62 33.76
"5" 33.91 34.11 34.36 34.57 34.79
"6" 34.12 34.16 34.32  34.4 34.45
end

Is it possible to directly merge these data sets or do i need to change the layout of the latter first? If so, which function provides the solution for this?

Best,

Tom

[Ken Chui]

For the second data, reshape it to long:

reshape long y, i(county) j(year)

Save that data somewhere. Then, for the first file, you'll need to delete those rows with multiple missing because they will interfere with the merging, then use a 1:1 merge to connect the data by year and county. Example code:

use data1, clear
drop if county == ""
merge 1:1 county year using data2

[tom berk]

Hi Ken Chui. Thank you, that worked perfectly fine