topic Re: Writing a proc sql join based on conditions in SAS Programming

Writing a proc sql join based on conditions

Jagadeesh2907 — Sun, 02 Oct 2016 10:17:51 GMT

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.

Re: Writing a proc sql join based on conditions

Reeza — Sun, 02 Oct 2016 10:38:26 GMT

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)

Re: Writing a proc sql join based on conditions

Patrick — Mon, 03 Oct 2016 00:17:03 GMT

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

Re: Writing a proc sql join based on conditions

Jagadeesh2907 — Mon, 03 Oct 2016 06:40:28 GMT

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.

Re: Writing a proc sql join based on conditions

Jagadeesh2907 — Mon, 03 Oct 2016 06:43:40 GMT

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

Re: Writing a proc sql join based on conditions

Patrick — Mon, 03 Oct 2016 07:50:31 GMT

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;

Re: Writing a proc sql join based on conditions

LinusH — Mon, 03 Oct 2016 07:52:30 GMT

Please provide sample input data and desired output.

Re: Writing a proc sql join based on conditions

Jagadeesh2907 — Mon, 03 Oct 2016 11:27:04 GMT

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.

Re: Writing a proc sql join based on conditions

Jagadeesh2907 — Mon, 03 Oct 2016 11:29:10 GMT

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.

Re: Writing a proc sql join based on conditions

Jagadeesh2907 — Mon, 03 Oct 2016 11:30:11 GMT

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