Proc SQL Delete, using a join

[rwnj]

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.

[Haikuo]

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;

[Anotherdream]

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

[Fugue]

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