Cartesian Join by Subject ID

jhsph07 — Fri, 10 May 2024 17:22:32 GMT

Hi,

I have 2 data sets like below.

Data one

A 1
A 2
B 1
B 2

Data two
A Apple
A Peach
B Banana

I need to join them (Cartesian join), but only join by ID (A and B).

This is what I want to get:

A 1 Apple
A 1 Peach
A 2 Apple
A 2 Peach
B 1 Banana
B 2 Banana

If I use the below code,

proc sql;
create table three as
select one.*
,two.*

from one
,two;
quit;

What I have got was

A 1 Apple
A 1 Peach
A 1 Banana
A 2 Apple
A 2 Peach
A 2 Banana
B 1 Apple
B 1 Peach
B 1 Banana
B 2 Apple
B 2 Peach
B 2 Banana

Is there any way to only join by the ID (A, B)?

Thank you very much!

Re: Cartesian Join by Subject ID

yabwon — Fri, 10 May 2024 17:47:33 GMT

proc sql;
create table three as
select one.*
,two.*
from one
,two
WHERE one.ID=two.ID
;
quit;

jhsph07 — Fri, 10 May 2024 18:19:01 GMT

Thanks a lot, it works!