Alternative data/proc step for sql not exists

[bhu]

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;

[ballardw]

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.

[bhu]

Thanks for reply.
Case I. Yes pass through is faster but I could not get the number of rows inserted.
Case II. I have a situation where I ended with pulling these datasets inside the WORK folder.

[ErikLund_Jensen]

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;