BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasban
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

View solution in original post

5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

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
PROC Star

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
Obsidian | Level 7

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_
Rhodochrosite | Level 12

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

sasban
Obsidian | Level 7

Thank you! Solved!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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