DATA Step, Macro, Functions and more

Proc SQL Delete, using a join

Reply
Contributor
Posts: 27

Proc SQL Delete, using a join

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.

Respected Advisor
Posts: 3,124

Re: Proc SQL Delete, using a join

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;

Super Contributor
Posts: 418

Re: Proc SQL Delete, using a join

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

Super Contributor
Posts: 307

Re: Proc SQL Delete, using a join

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


Ask a Question
Discussion stats
  • 3 replies
  • 4211 views
  • 0 likes
  • 4 in conversation