Proc SQL when condition contains missing value

JMD — Sat, 08 Feb 2014 04:30:03 GMT

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;

Re: Proc SQL when condition contains missing value

Tom — Sat, 08 Feb 2014 04:40:37 GMT

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)

Re: Proc SQL when condition contains missing value

JMD — Sat, 08 Feb 2014 04:43:20 GMT

You make my day!

Thanks, Tom.

topic Re: Proc SQL when condition contains missing value in SAS Programming

Proc SQL when condition contains missing value

Re: Proc SQL when condition contains missing value

Re: Proc SQL when condition contains missing value