DATA Step, Macro, Functions and more

Writing a proc sql join based on conditions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Writing a proc sql join based on conditions

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.

 

 


Accepted Solutions
Solution
‎10-03-2016 07:26 AM
Respected Advisor
Posts: 4,173

Re: Writing a proc sql join based on conditions

Posted in reply to Jagadeesh2907

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Writing a proc sql join based on conditions

Posted in reply to Jagadeesh2907

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)

Occasional Contributor
Posts: 9

Re: Writing a proc sql join based on conditions

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.

Occasional Contributor
Posts: 9

Re: Writing a proc sql join based on conditions

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.
Respected Advisor
Posts: 4,173

Re: Writing a proc sql join based on conditions

Posted in reply to Jagadeesh2907

"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)

 

Occasional Contributor
Posts: 9

Re: Writing a proc sql join based on conditions

I tried this, but still could not get the records for which X.a is missing. I am using sas 9.2.
Solution
‎10-03-2016 07:26 AM
Respected Advisor
Posts: 4,173

Re: Writing a proc sql join based on conditions

Posted in reply to Jagadeesh2907

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;
Occasional Contributor
Posts: 9

Re: Writing a proc sql join based on conditions

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.
Super User
Posts: 5,424

Re: Writing a proc sql join based on conditions

Posted in reply to Jagadeesh2907

Please provide sample input data and desired output.

Data never sleeps
Occasional Contributor
Posts: 9

Re: Writing a proc sql join based on conditions

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

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

Discussion stats
  • 9 replies
  • 453 views
  • 1 like
  • 4 in conversation