## Writing a proc sql join based on conditions

Solved
Occasional Contributor
Posts: 9

# 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
Posts: 4,736

## Re: Writing a proc sql join based on conditions

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
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;``````

All Replies
Super User
Posts: 23,683

## Re: Writing a proc sql join based on conditions

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
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.
Posts: 4,736

## Re: Writing a proc sql join based on conditions

"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
Posts: 4,736

## Re: Writing a proc sql join based on conditions

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
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,876

## Re: Writing a proc sql join based on conditions

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.