topic Re: proc sql : when key value is missing ! in SAS Procedures

proc sql : when key value is missing !

deega — Wed, 17 Aug 2016 01:57:26 GMT

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.

Re: proc sql : when key value is missing !

deega — Wed, 17 Aug 2016 02:20:01 GMT

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 !

Re: proc sql : when key value is missing !

PGStats — Wed, 17 Aug 2016 02:27:34 GMT

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)

Re: proc sql : when key value is missing !

deega — Wed, 17 Aug 2016 02:33:12 GMT

Tested, its working ! Thanks !

Re: proc sql : when key value is missing !

Reeza — Wed, 17 Aug 2016 03:20:32 GMT

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