How to use id1 to merge first , then use another id2 to merge for the rest?

[Olivia Li]

Good morning everyone, for a research that I'm performing I'm trying to merge dataset A and dataset B, my goal is to get the third table. Here is my design:

Firstly I want to use ID1 to merge, and some of them couldn;t be merged(like 3) .
Then I want to use ID2 to merge the unmerged ones.

Please could you be kindly to tell me how to realize this in stata?

Dataset-A:

id1	id2	sells
1	11	4
1	11	5
1	11	6
2	22	3
2	22	3
3	33	4
3	33	2

Dataset-B:

id1	id2	costs
1	11	0.2
2	.	0.3
.	33	0.4

Expected Dataset:

id1	id2	sells	costs
1	11	4	0.2
1	11	5	0.2
1	11	6	0.2
2	22	3	0.3
2	22	3	0.3
3	33	4	0.4
3	33	2	0.4

Thanks !!

[Øyvind Snilsberg]

use a, clear
merge m:1 id1 using b, keep(3) nogen
tempfile c
save `c'

use `c', clear
bys id1: keep if _n==1
merge 1:m id1 using a ,keep(2) nogen
drop costs
merge m:1 id2 using b ,keep(3) nogen

append using `c'

EDIT: alternatively,

use a, clear
joinby id1 using b, unmatched(master) _merge(_merge0)
joinby id2 using b, unmatched(master) update replace

[Olivia Li]

use a, clear
merge m:1 id1 using b, keep(3) nogen
tempfile c
save `c'

use `c', clear
bys id1: keep if _n==1
merge 1:m id1 using a ,keep(2) nogen
drop costs
merge m:1 id2 using b ,keep(3) nogen

append using `c'

EDIT: alternatively,

use a, clear
joinby id1 using b, unmatched(master) _merge(_merge0)
joinby id2 using b, unmatched(master) update replace

Thanks a lot !!
That really helps !!