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 ?
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;
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
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;
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.