topic Re: Proc SQL Delete, using a join in SAS Programming

Proc SQL Delete, using a join

rwnj — Tue, 13 Aug 2013 20:32:22 GMT

Hi,

In T-SQL I think I could write something like this:

Proc SQL;

delete a.*

from sample1 a inner join sample2 b

on a.id_src = b.id_src;

quit;

I basically want to remove records from one table that appear in the other.

Re: Proc SQL Delete, using a join

Haikuo — Tue, 13 Aug 2013 21:20:40 GMT

Something along the line of the following may work for you:

proc sql;

select * from sample1

where id_src not in (select id_src from sample2);

quit;

Re: Proc SQL Delete, using a join

Anotherdream — Tue, 13 Aug 2013 22:24:29 GMT

Are you doing this on a sql server database, or are you trying to do it on a sas dataset? Because there are multiple ways to do it and each ahs its own benefits and downsides.

Oh, if you are trying to do it on a sas dataset, the method above is the way I have done it in the past (with a slight modification).

Proc SQL;

delete

from sample1 ss1

where ss1.id_src in (select id_src from Sample2);

quit;

run;

I've never actually tried this on a multi key delete however.

If you're doing this on a rbms database (like sql server) or the like, it's actually far more efficient to use a sql pass through function, Let me know if you need help on this.

Thanks

Re: Proc SQL Delete, using a join

Fugue — Tue, 13 Aug 2013 23:15:36 GMT

Is this essentially the same question as https://communities.sas.com/message/177458#177458?