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.
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;
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)
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.
"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)
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;
Please provide sample input data and desired output.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: