topic Re: Alternative data/proc step for sql not exists in SAS Programming

Alternative data/proc step for sql not exists

bhu — Fri, 19 Apr 2019 23:00:20 GMT

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;

Re: Alternative data/proc step for sql not exists

ballardw — Fri, 19 Apr 2019 23:08:04 GMT

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.

Re: Alternative data/proc step for sql not exists

bhu — Fri, 19 Apr 2019 23:11:09 GMT

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.

Re: Alternative data/proc step for sql not exists

ErikLund_Jensen — Sat, 20 Apr 2019 08:05:57 GMT

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;