proc sql : when key value is missing !

[deega]

I wrote the following code :

proc sql;
create table new as
SELECT *
FROM A,B
where A.AID= B.BID ;

My output is almost as desired except the ones with missing values.
There are few observations in A where AID is missing and in final output
instead of getting the set of observations as it is, I am getting it repeated
n number of times. Could anybody please tell me the reason for the same.

[deega]

I have got one solution, I manually entered 0 wherever the key value was missing and then did left join as follows
proc sql;
create table base_3 as
SELECT *
FROM A
LEFT JOIN B
ON A.AID= B.BID ;
quit;

Its working... However, if there is a better way.. advise is welcome !

[PGStats · Accepted solution]

Instead of entering zeros by hand, you could do

proc sql;
create table base_3 as
SELECT *
FROM A
LEFT JOIN B
ON coalesce(A.AID,0)= B.BID ;
quit;

(untested)

[deega]

Tested, its working ! Thanks !

[Reeza]

It's your join type that causes the issue, just doing a left join should help, though they'd be blank, not 0