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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.