Hello,

I have tried to look for similar queries but couldn't find any. Thanks in advance for reading this.

I have two datasets: 1) is a panel ordered by (Code, Year); 2) Is basically a table that matches the Code in 1) with finer categories.
Example: code "10" represents CEOs and Legislators in 1) and corresponds with two categories in two "11-1011" (CEOs) and "11-1031" (Legislators).

I am using the following command: merge m:m ACSCode using ONET_ACS_crosswalk

Extract of dataset 2)(ONET_ACS_crosswalk) :
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 SOC2010Code str112 NationalEmploymentMatrixSOCO int ACSCode str128 ACSOccupationalTitle
"11-1031" "Legislators"      10 "Chief executives and legislators"
"11-1011" "Chief executives" 10 "Chief executives and legislators"
end
I would like to obtain a panel still ordered by (Code, Year) but having each code "10" corresponding to two categories. I'm having trouble in using merge m:m in order to match the two: Stata matches the first two rows with the proper categories but thereafter chooses just one of the two categories for all the rest of the couple (Code, Year). I will try to give you an idea here below with dataex, using the same example used before (Code "10" - CEOs and Legislators):
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input int ACSCode str156 ACSTitle int year str7 SOC2010Code str112 NationalEmploymentMatrixSOCO
10 "Chief executives and legislators" 2006 "11-1031" "Legislators"     
10 "Chief executives and legislators" 2006 "11-1011" "Chief executives"
10 "Chief executives and legislators" 2006 "11-1011" "Chief executives"
10 "Chief executives and legislators" 2006 "11-1011" "Chief executives"
....
10 "Chief executives and legislators" 2007 "11-1011" "Chief executives"
10 "Chief executives and legislators" 2007 "11-1011" "Chief executives"
10 "Chief executives and legislators" 2007 "11-1011" "Chief executives"
10 "Chief executives and legislators" 2007 "11-1011" "Chief executives"
....
end
The problem is the following: when it comes to the next year (2007), code "10" will only be matched by "11-1011". I would need, somehow, to merge BY (ACSCode Year), so to obtain for each (ACSCode Year) both categories "11-1011" and "11-1013".
I hope I have been somehow clear, I am sorry if I was not. Please ask me how to clarify if needed, any criticism or suggestion is highly appreciated.

Thanks,

Francesco