topic Re: How to do a left join conditioned on the values of a particular variable in the left table? in SAS Programming

How to do a left join conditioned on the values of a particular variable in the left table?

Shradha1 — Wed, 23 Jun 2021 14:20:43 GMT

I have two tables AC and DC, where the 'Account_Number' field is the merging key.

I want to do a left join on both these tables where table AC is the left table. But I want this join to happen only when the variable 'Month' in table AC takes value=5. But I want to retain all the observations of table AC and the new variables that are coming from table DC to AC will only have values in cases where Month=5.

Will this way work?

PROC SQL;
CREATE TABLE AC AS
SELECT A.*, B.* FROM AC(where=(month=5)) AS A
LEFT JOIN
DC AS B
ON A.KEY=B.KEY;
QUIT;

Please help. Thanks!

Re: How to do a left join conditioned on the values of a particular variable in the left table?

tarheel13 — Wed, 23 Jun 2021 14:26:35 GMT

I don't think I would write it that way. I would just use the where clause in SQL. If a and b have any of the same columns, SAS is going to put a warning in the log that the column exists in more than one table.

Proc SQL;
create table AC as 
select a.*,b.*
from ac as a 
left join 
dc as b
on a.key=b.key
where a.month=5;
quit;

Re: How to do a left join conditioned on the values of a particular variable in the left table?

Shradha1 — Wed, 23 Jun 2021 14:30:53 GMT

This works. Thanks a lot!