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;
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)
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)
You make my day!
Thanks, Tom.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.