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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.