SAS join

[kajal_30]

Hi I am using a sub query  

FROM cars WHERE ID NOT IN (select distinct ID from cars_m1 where data_date = "31MAR2018"d ) 

is there a way I can use a left or right join to eliminate the sub query ?

[yabwon]

Hi,

Do you mean to get something like this:

ods html;
data cars(keep = id text) cars_m1(drop=text);
input ID data_date date9. text $;
cards;
1 31MAR2018 A
2 31MAR2018 B
3 31MAR2018 C
4 01APR2018 D
5 01APR2018 E
6 01APR2018 F
;
run;

proc sql;
select cars.* from
cars 
join
(select distinct ID from cars_m1 where NOT (data_date = "31MAR2018"d)) x
on cars.id = x.id
;


select distinct cars.* from
cars 
join
cars_m1 x
on cars.id = x.id
and NOT (x.data_date = "31MAR2018"d)
;

quit;

all the best

Bart

[ChrisNZ · Accepted solution]

If you want a left join ( but you are really doing an inner join here), these 2 queries are equivalent:

data T; 
  set SASHELP.CARS; 
  DATA_DATE =ifn(ranuni(1) > .5, "31MAR2018"d,"01MAR2018"d );     
  ID=MODEL;
run;

proc sql;   

create table T1 as
select *
from T 
where ID not in (select distinct ID from T where DATA_DATE = "31MAR2018"d );     
 
create table T1 as
select T.*
from T 
     left join 
     (select distinct ID from T where DATA_DATE = "31MAR2018"d ) tt 
     on t.ID =tt.ID  
where tt.ID is null;

quit;