Proc SQL when condition contains missing value

[JMD]

Good evening,

I try to merge 2 file using sql. The condition file contains missing value since I put all condition in one file. For those with missing, SQL pick up no record.

In the code below, if wish that time=2 should pick up the second record in "source" data as two=6 and three=8.

Any suggestion of how to correct the SQL or of other method is very much appreciated.

JHN

data condition;
input time one two three ;
datalines;
1 3 4 5
2 . 6 8
3 2 . .
4 9 6 .
;;;;

data source;
input number_client one two three ;
datalines;
7 3 4 5
8 3 6 8
9 2 5 6
10 2 6 9
11 3 6 8
12 3 6 9
;;
run;

proc sql;
create table want
as select * from condition as a left join source as b
on a.one=b.one and a.two=b.two and a.three=b.three; quit;

[Tom · Accepted solution]

Try this and see if it helps.

on (a.one = b.one or a.one is null)

and (a.two = b.two or a.two is null)

and (a.three = b.three or a.three is null)

[JMD]

You make my day!

Thanks, Tom.