Where X in Y condition in Proc SQL Join

[Shradha1]

I want to join dataset X &B to create a new dataset C., joining key being account number.

But I want to use only those observations from dataset X where account number is present in another datset Z.

I tried this code but its giving an error

PROC SQL;
CREATE TABLE C AS
SELECT A*,B.*SPEND FROM 
X WHERE ACCT_NUM IN (SELECT DISTINCT ACCT_NUM FROM Z)  AS A
LEFT JOIN
B AS B
ON A.ACCT_NUM=B.ACCT_NUM;
QUIT;

What is the correct way to do this?

[tarheel13]

Is it B.* or B.spend? It looks like a comma could be missing. And if acct_num is in both table A and table B then you will have to put either A.acct_num or B.acct_num to distinguish which one you want to use. I also think you need to put the where statement after the join.

[FreelanceReinh]

Hi @Shradha1,

I think you'll get a better performance if you avoid the IN operator and the associated subquery by using an inner join with dataset Z ([edit:] more precisely: the set of distinct account numbers from Z) instead.

Something like this:

proc sql;
create table c as
select x.*, b.spend from 
x join (select distinct acct_num from z) z
on x.acct_num=z.acct_num
left join b
on x.acct_num=b.acct_num;
quit;

[blueskyxyz]

proc sql;
    create table test as 
    select a.*, c.lower
    from sashelp.class as a inner join
                                (select  name
                                from sashelp.class
                                where sex = 'M') as b
                       on a.name=b.name

                       left join sashelp.classfit as c
                       on a.name=c.name
;
quit;

[Kurt_Bremser]

Add another join (sub-selects perform notoriously bad in SAS SQL):

proc sql;
create table c as
  select
    a.*,
    b.spend
  from a
  left join b
  on a.acct_num = b.acct_num
  inner join z
  on a.acct_num = z.acct_num
;
quit;

BTW there is no need for shouting at the SAS interpreter, it respects lowercase perfectly well.