Merge 1:m separately by group

[Rob Zanelli]

Dear Community,

Is there a way how to use the 1:m merge command separately by a group-identifier? So that it kind of looks at each group, does the merging, goes to ge next group and does the merging again?

My concrete case is the following:

dataset1:

group	code1
a	1115
a	1116
a	1117
a	1118
b	1115
b	1116
b	1117
b	1118
c	1115
c	1116
...	...

dataset2:

code1	code2
1115	2001
1115	2002
1115	2003
1116	2001
1116	2004
1117	2005
1117	2006
1118	2002
1118	2003
1118	2004
1118	2005

I want to merge dataset 1 and dataset 2, such that I end up with a table where all codes2 are assigned to the respective code 1 in all groups separately.

group	code1	code2
a	1115	2001
a	1115	2002
a	1115	2003
a	1116	2001
a	1116	2004
a	1117	2005
a	1117	2006
...	...	...
b	1115	2001
b	1115	2002
b	1115	2003
b	1116	2001
b	1116	2004
...	...	...
...	...	...

What I tried is using the detested m:m command "merge m:m code1 using dataset2.dat". However, then it of course assigned only one value to 1115 in each group and went on to 1115 in the next group. I theoretically get exactly what I want if I only have one group (using 1:m).

Therefore my question: How can I tell STATA that is shall complete the merge within one group (i.e. assigning all values to 1115 of group a) before going on to the next one? I was looking at the help-file and googled around, but could not find an appropriate solution. Worst case, I could create a loop, merge each group individually and append it again. However, I try to avoid such inefficient coding.

I hope, I was able to describe my problem in an understandable way.

Thank you for your responses!

Kind Regards,
Roman

Edit: I am using STATA 13.

[Mike Lacy]

Whenever -merge m:m- comes to mind, look at -joinby-. I believe this is what you want or something close:

input code1 code2
1115  2001
1115  2002
1115  2003
1116  2001
1116  2004
1117  2005
1117  2006
1118  2002
1118  2003
1118  2004
1118  2005
end
tempfile temp2
save `temp2'
//
clear
input str1 group code1
a  1115
a  1116
a  1117
a  1118
b  1115
b  1116
b  1117
b  1118
c  1115
c  1116
end
joinby code1 using `temp2'
// display
sort group code1 code2
list

[Rob Zanelli]

Dear Mike,

Such an easy solution, thank you so much for this promt and helpful answer. This is exactly what I was looking for!

Greetings from Switzerland and have a nice day
Roman