Can we say match merge is like inner join in sql?

[GN0001]

Hello all,

Can we say that match merge is like inner join in sql?

Please advise me.

Regards,

blue blue

[PaigeMiller]

Yes, it is "like" inner join.

No, it is not identical to inner join.

[GN0001]

Hello Paige Miller,

You would say: "Yes, it is "like" inner join."

Doesn't it pull when the matches exist on left and right table?

then

You would say: "No, it is not identical to inner join."

Can you please elaborate it?

Regards,

Blue blue

[Reeza]

Test what happens if you have duplicates in each tables.

[PaigeMiller]

---

@GN0001 wrote:

Hello Paige Miller,

You would say: "Yes, it is "like" inner join."

Doesn't it pull when the matches exist on left and right table?

then

You would say: "No, it is not identical to inner join."

---

I think you need to ask more specific and detailed questions. In my mind, on some criteria, merge is indeed "like" an inner join. On other criteria, merge is not "like" an inner join.

People have given examples of differences. There are probably other differences as well.

[ballardw]

Here's a way to test this:

Make two data sets with the same named variables with the first variable all having the same value. Then do a match merge and an inner join on the two. Look at the results.

Similar- maybe under specific conditions. Depend on how loose your criteria is for setting likeness.

Data set1;
   input x y;
datalines;
1 1
1 2
1 3
1 4
;

data set2;
   input x y;
datalines;
1  11
1  22
1  33
;

[GN0001]

Hello,

This is what I have, I can't understand what happens and how to control it. 

Thanks,

blue blue

Data set1;
Input x y;
dataline;
1 1
1 2
1 3
1 4
;


Data set2;
Input x z;
dataline;
1 11
1 22
1 33
;


data combined;
Merge set2 set1;
Run;

[Kurt_Bremser]

---

@GN0001 wrote:

Hello,

This is what I have, I can't understand what happens and how to control it. 

Thanks,

blue blue

Data set1;
Input x y;
dataline;
1 1
1 2
1 3
1 4
;


Data set2;
Input x z;
dataline;
1 11
1 22
1 33
;


data combined;
Merge set2 set1;
Run;

---

You have neither a BY statement nor a Subsetting IF, so this ain't a match merge, you just put both datasets side-by-side.

Since you also have repeats of the common variable x in both datasets, you can't replicate the behavior of SQL (you have a many-to-many join).

[GN0001]

Hello team,
I think I understood this part.
Regards,
blue blue

[Kurt_Bremser]

Post the code you are trying.

[Ksharp]

1:1 or 1:n match is the same with both,
But n:n match is different .

of course, you need some following code to do like INNER JOIN .
data want;
merge a(in=ina) b(in=inb);
by id;
if ina and inb;
run;