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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.