DATA Step, Macro, Functions and more

SQL Code to combine tables having many to many relation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

SQL Code to combine tables having many to many relation

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 

Swain

Accepted Solutions
Solution
‎06-30-2016 09:06 AM
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 17,776

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

What is the expected output?

Frequent Contributor
Posts: 96

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

Hi Reeza,

 

Expected output is attached for your kind consideration. 

 

Regards,

Deepak

Swain
Super User
Posts: 17,776

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

Please post as text rather than an attachment. 

Solution
‎06-30-2016 09:06 AM
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 461 views
  • 2 likes
  • 3 in conversation