Help using Base SAS procedures

proc sql : when key value is missing !

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

proc sql : when key value is missing !

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.


Accepted Solutions
Solution
‎08-16-2016 11:24 PM
Respected Advisor
Posts: 4,920

Re: proc sql : when key value is missing !

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)

PG

View solution in original post


All Replies
Contributor
Posts: 71

Re: proc sql : when key value is missing !

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 !
Solution
‎08-16-2016 11:24 PM
Respected Advisor
Posts: 4,920

Re: proc sql : when key value is missing !

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)

PG
Contributor
Posts: 71

Re: proc sql : when key value is missing !

Tested, its working ! Thanks !
Super User
Posts: 19,785

Re: proc sql : when key value is missing !

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 436 views
  • 1 like
  • 3 in conversation