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

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

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

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