topic Re: Can we say match merge is like inner join in sql? in SAS Programming

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

GN0001 — Thu, 22 Jul 2021 21:44:09 GMT

Hello all,

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

Please advise me.

Regards,

blue blue

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

PaigeMiller — Thu, 22 Jul 2021 21:52:07 GMT

Yes, it is "like" inner join.

No, it is not identical to inner join.

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

GN0001 — Thu, 22 Jul 2021 21:55:14 GMT

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

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

Reeza — Thu, 22 Jul 2021 21:56:59 GMT

Test what happens if you have duplicates in each tables.

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

ballardw — Thu, 22 Jul 2021 21:57:57 GMT

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
;

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

PaigeMiller — Thu, 22 Jul 2021 22:20:39 GMT

@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.

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

Kurt_Bremser — Fri, 23 Jul 2021 06:12:37 GMT

Post the code you are trying.

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

Ksharp — Fri, 23 Jul 2021 12:30:40 GMT

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;

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

GN0001 — Sat, 24 Jul 2021 03:11:00 GMT

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;

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

GN0001 — Sat, 24 Jul 2021 03:25:14 GMT

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

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

Kurt_Bremser — Sat, 24 Jul 2021 05:06:14 GMT

@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).