topic Re: Merging - many to many relationship in SAS Programming

Merging - many to many relationship

mlogan — Tue, 29 Mar 2016 18:39:51 GMT

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

Re: Merging - many to many relationship

SASKiwi — Tue, 29 Mar 2016 18:54:21 GMT

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;

Re: Merging - many to many relationship

cidzorek — Tue, 29 Mar 2016 19:01:09 GMT

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;