DATA Step, Macro, Functions and more

Left join- missing rows from left table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Left join- missing rows from left table

While joining table (soapnotes) to variables from database, I'm getting multiple rows (observation) blank. Any suggestions, please? 

 

 

proc sql;

create table q.get_mem_no0 as

select a.*,b.document as doc_wh , b.lob, b.member , b.first_dos, c.recip_no as recipno

from

q.soapnotes a

left join

am.MASTER_CLAIM b on a.document = b.document

left join am.MEMBERS c on b.member = c.mem_no

;

quit;


Accepted Solutions
Solution
‎02-06-2018 08:45 PM
PROC Star
Posts: 549

Re: Left join- missing rows from left table

this means you have null values in your driver table to begin with 

View solution in original post


All Replies
Super User
Posts: 2,516

Re: Left join- missing rows from left table

[ Edited ]

This can happen. For example:

data HAVE1;
  A=' ';
run;
data HAVE2;
  A=' '; output; 
  A=' '; output; 
run;
data HAVE3;
  A=' '; output; 
  A=' '; output; 
  A=' '; output; 
run;
proc sql; 
  select HAVE1.*, monotonic() from HAVE1 left join HAVE2 on HAVE1.A=HAVE2.A left join HAVE3 on HAVE2.A=HAVE3.A;
quit;

 



A  
  1
  2
  3
  4
  5
  6
Super User
Posts: 4,030

Re: Left join- missing rows from left table

The problem is your second LEFT JOIN. Since your first LEFT JOIN would allow missing values for MASTER_CLAIM b.member, these will be repeated for every missing value of c.mem_no. Try this:

proc sql;
create table q.get_mem_no0 as
select a.*,b.document as doc_wh , b.lob, b.member , b.first_dos, c.recip_no as recipno
from
q.soapnotes a
left join
am.MASTER_CLAIM b on a.document = b.document
left join am.MEMBERS c on b.member = c.mem_no
and not missing(b.member)
;
quit;
Occasional Contributor
Posts: 17

Re: Left join- missing rows from left table

Thank you for your suggestions. Just doing first left join also have same problem. I tried with the code you provided, again same result.

Solution
‎02-06-2018 08:45 PM
PROC Star
Posts: 549

Re: Left join- missing rows from left table

this means you have null values in your driver table to begin with 

Occasional Contributor
Posts: 17

Re: Left join- missing rows from left table

Thank you! Solved!!

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 563 views
  • 0 likes
  • 4 in conversation