Help using Base SAS procedures

Fetching the column in PROC SQL where the outer join gets performed

Reply
Contributor
Posts: 59

Fetching the column in PROC SQL where the outer join gets performed

Hi,

I have been using the below query to perform outer join on a column

Proc Sql;

create table All_Acc as select

a.acct,
a.year,
b.acct,
b.sysdate,
b.fin,
b.mnts

from
Accts_abc a full outer join Accts_def b
on a.acct = b.acct
;

The problem now is that any value for acct which is in b and not in a is giving me a null value on that column. Even if i reverse the join condition b on a , it happens for acct values present in a and not in b.

I cant use a MERGE at datastep level (if a or b) since i ll have to sort the above 2 datasets, which I am trying to avoid due to the Temp Space it requires.

Basically, I thought the above PROC SQL should have wrkd the same as merge, and kept acct values whereever it is in the 2 datasets.

Can someone pls suggest a way out.

Thanks
Tej
Super User
Posts: 5,441

Re: Fetching the column in PROC SQL where the outer join gets performed

Posted in reply to tejeshwar
You should always use COALESCE() function on common columns when performing a full join.

/Linus
Data never sleeps
Contributor
Posts: 59

Re: Fetching the column in PROC SQL where the outer join gets performed

Posted in reply to tejeshwar
Thanks so much, it resolved my problem to getting null rows on the merging_key
Ask a Question
Discussion stats
  • 2 replies
  • 94 views
  • 0 likes
  • 2 in conversation