BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jagadeesh2907
Obsidian | Level 7

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

9 REPLIES 9
Reeza
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7
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
Opal | Level 21

"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
Obsidian | Level 7
I tried this, but still could not get the records for which X.a is missing. I am using sas 9.2.
Patrick
Opal | Level 21

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
Obsidian | Level 7
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
Tourmaline | Level 20

Please provide sample input data and desired output.

Data never sleeps
Jagadeesh2907
Obsidian | Level 7
Hi, I got the solution. it was not working since I did not create alias for the joining tables. thank you.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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