DATA Step, Macro, Functions and more

Merging - many to many relationship

Accepted Solution Solved
Reply
Regular Contributor
Posts: 218
Accepted Solution

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,256

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;

View solution in original post


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

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.

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

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