Proc SQL when condition contains missing value

Accepted Solution Solved
Reply
Occasional Contributor JMD
Occasional Contributor
Posts: 5
Accepted Solution

Proc SQL when condition contains missing value

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;


Accepted Solutions
Solution
‎02-07-2014 11:40 PM
Super User
Super User
Posts: 6,502

Re: Proc SQL when condition contains missing value

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)

View solution in original post


All Replies
Solution
‎02-07-2014 11:40 PM
Super User
Super User
Posts: 6,502

Re: Proc SQL when condition contains missing value

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)

Occasional Contributor JMD
Occasional Contributor
Posts: 5

Re: Proc SQL when condition contains missing value

You make my day!

Thanks, Tom.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 237 views
  • 0 likes
  • 2 in conversation