## Merging - many to many relationship

Solved
Regular Contributor
Posts: 244

# Merging - many to many relationship

[ Edited ]

Can someone please tell me what would be the code for Merging in a Many to Many relationship. Thanks,

Table 1:

ID State
101 NY
101 TX
103 NY
104 NY
102 TX

Table 2:
State Day Time
NY MON 7:00 - 8:00
NY TUES 10:00 - 10:30
NY WED 11: - 11:45
TX FRI 8:00 - 9:30
TX SAT 10:00 - 11:00
TX MON 9:00 - 11:00

Output:
ID State Day Time
101 NY MON 7:00 - 8:00
101 NY TUES 10:00 - 10:30
101 NY WED 11: - 11:45
101 TX FRI 8:00 - 9:30
101 TX SAT 10:00 - 11:00
101 TX MON 9:00 - 11:00
103 NY MON 7:00 - 8:00
103 NY TUES 10:00 - 10:30
103 NY WED 11: - 11:45
104 NY MON 7:00 - 8:00
104 NY TUES 10:00 - 10:30
104 NY WED 11: - 11:45
102 TX FRI 8:00 - 9:30
102 TX SAT 10:00 - 11:00
102 TX MON 9:00 - 11:00

Accepted Solutions
Solution
‎03-29-2016 04:20 PM
Super User
Posts: 3,927

## Re: Merging - many to many relationship

``````proc sql;
create table want as
select A.ID
,A.State
,B.Day
,B.Time
from Table1 as A
inner join Table2 as B
on A.State = B.State
;
quit;``````

All Replies
Solution
‎03-29-2016 04:20 PM
Super User
Posts: 3,927

## Re: Merging - many to many relationship

``````proc sql;
create table want as
select A.ID
,A.State
,B.Day
,B.Time
from Table1 as A
inner join Table2 as B
on A.State = B.State
;
quit;``````
Senior User
Posts: 1

## Re: Merging - many to many relationship

I'd use a PROC SQL statement and do a full outer join:

PROC SQL;

CREATE TABLE OUTPUT AS

SELECT t1.ID,

t1.State,

t2.Day,

t2.Time

FROM TABLE1 t1

FULL JOIN TABLE2 t2 ON (t1.State = t2.State);

QUIT;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 2 replies
• 250 views
• 3 likes
• 3 in conversation