topic SQL Code to combine tables having many to many relation in SAS Programming

SQL Code to combine tables having many to many relation

DeepakSwain — Wed, 29 Jun 2016 20:13:59 GMT

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

Re: SQL Code to combine tables having many to many relation

Reeza — Wed, 29 Jun 2016 20:16:55 GMT

What is the expected output?

Re: SQL Code to combine tables having many to many relation

DeepakSwain — Wed, 29 Jun 2016 20:22:12 GMT

Hi Reeza,

Expected output is attached for your kind consideration.

Regards,

Deepak

Re: SQL Code to combine tables having many to many relation

Reeza — Wed, 29 Jun 2016 20:31:31 GMT

Please post as text rather than an attachment.

Re: SQL Code to combine tables having many to many relation

Kurt_Bremser — Thu, 30 Jun 2016 08:00:08 GMT

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.