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?
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.
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.