I have a condition where I have to use "where not exists" in proc sql. The problem with this is, it runs extremely fast inside the SQL server, but this is very very slow using SAS. So I need an alternative for it in SAS.
proc sql;
insert into table1
select * from table2 a
where not exists
(
select * from table3 b
where a.cond1=b.cond1 and a.cond2=b.cond2
)
;
quit;
Use passthrough code to execute the query on the server might be best approach then.
If table3 is in a remote DBMS then there is going to be a lot of overhead and if the other two tables are also on the server then passthrough to execute in the DBMS would really be the way to go.
Hi @bhu
If your table2 and table3 are SAS WORK tables this might be a useful approach:
proc sql;
create table work.insert as
select * from work.table2 a
where not exists (
select * from work.table3 b
where a.v1=b.v1 and a.v2=b.v2
);
quit;
proc append
base = sqllib.table1
data = work.insert;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.