Help: how to combine information from two datasets?

[Christine Everaerts]

Hi all,

I was wondering if someone could help me with this problem: I need to combine information of two datasets mentioned below.


So for example, in the dataset on NTM's there is this observation:

Imposing	Affected	NTM	HS96
Cyprus	World	SPS	0102

Since this NTM is imposed on a 4-digit level, it is also imposed on its underlying 6-digit levels. So I need the example above to become like this:

Imposing	Affected	NTM	HS96
Cyprus	World	SPS	010210
Cyprus	World	SPS	010290

Also if the HS96 is only a 2-digit level in the NTM's dataset, I need it to be converted to all of its corresponding 6-digit levels.
Is there someone who could help me out with this problem?

Thank you in advance!




Code for all HS digit levels in HS96 versions : (contains all the 2-digit levels and its corresponding 4- and 6-digit levels)

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 HS96
"01"    
"0101"  
"010111"
"010119"
"010120"
"0102"  
"010210"
"010290"
"0103"  
"010310"
"010391"
"010392"
"0104"  
"010410"
"010420"
"0105"  
"010511"
"010512"
"010519"
"010592"
"010593"
"010599"
"0106"  
"010600"
"02"    
"0201"  
"020110"
"020120"
"020130"
"0202"  
"020210"
"020220"
"020230"
"0203"  
"020311"
"020312"
"020319"
"020321"
"020322"
"020329"
"0204"  
"020410"
"020421"
"020422"
"020423"
"020430"
"020441"
"020442"
"020443"
"020450"
"0205"  
"020500"
"0206"  
"020610"
"020621"
"020622"
"020629"
"020630"
"020641"
"020649"
"020680"
"020690"
"0207"  
"020711"
"020712"
"020713"
"020714"
"020724"
"020725"
"020726"
"020727"
"020732"
"020733"
"020734"
"020735"
"020736"
"0208"  
"020810"
"020820"
"020890"
"0209"  
"020900"
"0210"  
"021011"
"021012"
"021019"
"021020"
"021090"
"03"    
"0301"  
"030110"
"030191"
"030192"
"030193"
"030199"
"0302"  
"030211"
"030212"
"030219"
"030221"
end

Code for dataset denoting NTM's imposed on Japan or World: (contains all NTM's imposed on different HS digit levels)

* Example generated by -dataex-. For more info, type help dataex
clear
input str109 Imposing str501 Affected str11 NTM str27 HS96
"Cyprus"                   "World" "SPS  " " 0102"
"Switzerland"              "World" "SPS  " " 0102"
"United States of America" "World" "SPS  " " 0102"
"United States of America" "World" "SPS  " " 0102"
"Thailand"                 "World" "SPS  " " 0102"
"Philippines"              "World" "SPS  " " 0102"
"Switzerland"              "World" "SPS  " " 0102"
"Singapore"                "World" "SPS  " " 0103"
"Mexico"                   "World" "SPS  " " 0103"
"Hong Kong, China"         "World" "SPS  " " 0103"
"Hong Kong, China"         "World" "SPS  " " 0103"
"Panama"                   "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"New Zealand"              "World" "SPS  " " 0103"
"Hong Kong, China"         "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"European Union"           "World" "SPS  " " 0103"
"Pakistan"                 "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"Cyprus"                   "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"Poland"                   "World" "SPS  " " 0103"
"European Union"           "World" "SPS  " " 0103"
"Malaysia"                 "World" "SPS  " " 0103"
"Philippines"              "World" "SPS  " " 0103"
"Thailand"                 "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"European Union"           "World" "SPS  " " 0103"
"European Union"           "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"Switzerland"              "World" "SPS  " " 0103"
"Indonesia"                "World" "SPS  " " 0103"
"Hong Kong, China"         "World" "SPS  " " 0103"
"Czech Republic"           "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"Hong Kong, China"         "World" "SPS  " " 0103"
"Switzerland"              "World" "SPS  " " 0103"
"Slovak Republic"          "World" "SPS  " " 0103"
"United States of America" "World" "SPS  " " 0103"
"Panama"                   "World" "SPS  " " 0103"
"European Union"           "World" "SPS  " " 0103"
"European Union"           "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Philippines"              "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Indonesia"                "World" "SPS  " " 0104"
"European Union"           "World" "SPS  " " 0104"
"Slovak Republic"          "World" "SPS  " " 0104"
"European Union"           "World" "SPS  " " 0104"
"Philippines"              "World" "SPS  " " 0104"
"Indonesia"                "World" "SPS  " " 0104"
"Poland"                   "World" "SPS  " " 0104"
"Hong Kong, China"         "World" "SPS  " " 0104"
"Hong Kong, China"         "World" "SPS  " " 0104"
"Switzerland"              "World" "SPS  " " 0104"
"Philippines"              "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Canada"                   "World" "SPS  " " 0104"
"Romania"                  "World" "SPS  " " 0104"
"Philippines"              "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Slovak Republic"          "World" "SPS  " " 0104"
"Panama"                   "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Panama"                   "World" "SPS  " " 0104"
"Mexico"                   "World" "SPS  " " 0104"
"New Zealand"              "World" "SPS  " " 0104"
"Thailand"                 "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Australia"                "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Panama"                   "World" "SPS  " " 0104"
"Hong Kong, China"         "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Hong Kong, China"         "World" "SPS  " " 0104"
"Panama"                   "World" "SPS  " " 0104"
"Malaysia"                 "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"European Union"           "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Hong Kong, China"         "World" "SPS  " " 0104"
"Switzerland"              "World" "SPS  " " 0104"
"Czech Republic"           "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"Cyprus"                   "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0104"
"United States of America" "World" "SPS  " " 0105"
"United States of America" "World" "SPS  " " 0105"
end

[alejoforero]

I would do multiple m:1 merges to the NTM data:

use your_ntm_data, clear

g length_ntm=length(hs96)

preserve
    keep if length_ntm==2
    rename hs96 hs96_2d
    save temp_ntms_2digits, replace
restore, preserve
    keep if length_ntm==4
    rename hs96 hs96_4d
    save temp_ntms_4digits, replace
restore, preserve
    keep if length_ntm==6
    save temp_ntms_6digits, replace
restore

use your_hs6data, clear

g hs96_2d=substr(hs96,1,2)
g hs96_4d=substr(hs96,1,4)

*Merge the 6 digits
merge m:1 hs96 using temp_ntms_6digits, 

*Merge the 4 digits
merge m:1 hs96_4d using temp_ntms_4digits, update

*Merge the 2 digits
merge m:1 hs96_2d using temp_ntms_2digits, update

notice that it is possible that one product gets different NTMS at different levels, say one at the 6 digits level, one at 4 digits level, etc.

[Christine Everaerts]

Thank you for helping!
However, I was wondering how I can do a m:1 merge using the temporary files?
If I try the command, I get the notification that the variables in the using data aren't uniquely defined. As on the dataset I put in my previous post, multiple countries impose a NTM on the same HScode.

Is there any other way how it can be done?