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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.