BookmarkSubscribeRSS Feed
rwnj
Calcite | Level 5

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.

3 REPLIES 3
Haikuo
Onyx | Level 15

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
Quartz | Level 8

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
Quartz | Level 8

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 13491 views
  • 1 like
  • 4 in conversation