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

tejeshwar — Sun, 18 Jan 2009 06:28:41 GMT

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

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

LinusH — Mon, 19 Jan 2009 12:48:12 GMT

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

/Linus

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

tejeshwar — Wed, 21 Jan 2009 03:01:58 GMT

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

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

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

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

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