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

[tejeshwar]

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

[LinusH]

You should always use COALESCE() function on common columns when performing a full join.

/Linus

[tejeshwar]

Thanks so much, it resolved my problem to getting null rows on the merging_key