SQL Code to combine tables having many to many relation

[DeepakSwain]

Hi there,

I am looking for a sql code to combine two tables: admission and insurance where a person having coverage in part by 2 insurance companies following each admission. I came across example in community that suggest use of multiple set statements. I am interested to know the possible solution using sql code.

data admission;
input id $ date $ ;
datalines;
1 10102015
1 11103015
;
run;


data insurance;
input id $ company $;
datalines;
1 MOH
1 sunlife
;
run;

/*merge  not allow many to many relation*/
proc sort data =admission; by id ; run;
proc sort data =insurance; by id ; run;
data merged;
merge admission (in=a) insurance(in=b);
by id;
if a=b;
run;

Thank you in advance for your kind reply.

Regards,

Deepak 

[Reeza]

What is the expected output?

[DeepakSwain]

Hi Reeza,

Expected output is attached for your kind consideration. 

Regards,

Deepak

[Reeza]

Please post as text rather than an attachment. 

[Kurt_Bremser · Accepted solution]

Your SQL step might look like that:

proc sql;
create table merged as
select
  a.*,
  b.company
from admission a, insurance b
where a.id = b.id;
;
quit;

The resulting table would look like this:

Obs    id      date      company

 1     1     10102015    MOH    
 2     1     10102015    sunlife
 3     1     11103015    MOH    
 4     1     11103015    sunlife

Mind that I won't open a MS Office document from the web. Post data as a datastep or in text form.