Writing a proc sql join based on conditions

[Jagadeesh2907]

I have a situation where i have to check a column A and according to that decide the column to use in the join condition. For Example: If column A is null, i need to do left join of tables Table X and Table Y on column B and if Column A is not null, then i need to left join Table X and Y on the column A. Could you please help me how to approach a solution to this problem.

[Reeza]

You can have conditions in your join. 

Is is this what you mean:

on case when x.a is null then x.b else x.a end = case when y.a is null then y.b else y.a end 

Another option is COALESCE type function. Assuming a character variable 

coalescec(x.a, x.b) = coalescec(y.a, y.b)

[Jagadeesh2907]

i tried this, but i am not able to get the records for which the column x.a is null. please find one example below:

data a ;
infile datalines dlm = ',' ;
input first $ last $;
datalines;
sri,ram
ram,pra
 ,ali
;
run;

data b;
infile datalines dlm = ',' ;
input third $ fourth $ fifth $ ;
datalines;
sri,jagu,good
ram,weas,bad
abdul,ali,best
;
run;

Method 1:

proc sql ;
create table c as
select
a.first,
a.last,
b.fourth,
b.fifth
from a left join b
on case when a.first is null then a.last else a.first end = case when a.first is null then b.fourth else b.third end;
quit

Method 2:

proc sql ;
create table c as
select
a.first,
a.last,
b.fourth,
b.fifth
from a left join b
on (a.first is NULL and a.last=b.fourth)  or  (a.first is not NULL and a.first=b.third);
quit;

Method 3:

proc sql ;
 create table c as
 select
 a.first,
 a.last,
 b.fourth,
 b.fifth
 from a left join b
on coalescec(a.first,a.last) = coalescec(b.third,b.fourth);
quit;

In the final result, I am expecting a result with 3 rows where i need the third record to be matched for the value 'ali' since 'Abdul' is missing in the first table. please suggest.

[Jagadeesh2907]

Thank you Reeza. I could not use coalescec since I have missing values only on one table and not on both tables used in the join. however, using the case statement works fine now. Earlier I missed to create alias when joining the table hence they were not working. Many thanks for the solution.

[Patrick]

"If column A is null, i need to do left join of tables Table X and Table Y on column B and if Column A is not null, then i need to left join Table X and Y on the column A"

For the given example, couldn't the join condition just look as below?

  X left join Y
  on 
    (x.a is NULL and x.B=y.B)
    or (x.a is not NULL and x.A=y.A)

[Jagadeesh2907]

I tried this, but still could not get the records for which X.a is missing. I am using sas 9.2.

[Patrick · Accepted solution]

It works for me. Does below give you the result you're after?

data a;
  infile datalines dlm = ',';
  input first $ last $;
  datalines;
sri,ram
ram,pra
 ,ali
;
run;

data b;
  infile datalines dlm = ',';
  input third $ fourth $ fifth $;
  datalines;
sri,jagu,good
ram,weas,bad
abdul,ali,best
;
run;

proc sql;
  create table want as
  select a.*, b.*
  from a as X left join b as Y
  on     (x.first is NULL and x.last=y.fourth)
    or (x.first is not NULL and x.first=y.third)
  ;
quit;

[Jagadeesh2907]

Thank you Patrick. It works fine now. what mistake I did was I did not create alias for table names and used them as such. Many thanks.

[LinusH]

Please provide sample input data and desired output.

[Jagadeesh2907]

Hi, I got the solution. it was not working since I did not create alias for the joining tables. thank you.