Left join- missing rows from left table

[sasban]

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;

[ChrisNZ]

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

[SASKiwi]

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;

[sasban]

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

[kiranv_ · Accepted solution]

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

[sasban]

Thank you! Solved!!