topic Left join- missing rows from left table in SAS Programming

Left join- missing rows from left table

sasban — Wed, 07 Feb 2018 00:09:09 GMT

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;

Re: Left join- missing rows from left table

ChrisNZ — Wed, 07 Feb 2018 01:16:41 GMT

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

Re: Left join- missing rows from left table

SASKiwi — Wed, 07 Feb 2018 01:22:47 GMT

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;

Re: Left join- missing rows from left table

sasban — Wed, 07 Feb 2018 01:28:49 GMT

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

Re: Left join- missing rows from left table

kiranv_ — Wed, 07 Feb 2018 01:33:16 GMT

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

Re: Left join- missing rows from left table

sasban — Wed, 07 Feb 2018 01:45:50 GMT

Thank you! Solved!!