How to delete duplicate data based on matching data in two table?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 144
Accepted Solution

How to delete duplicate data based on matching data in two table?

Hello everybody,

I have two tables which are shown below:

 

Table 01
DateTimeNamePrice
11asd1
12asd2
12sdf1
14sdf2
21dfg1
22sdf2
31asd3
32fgh1
33fgh2

 

Table 02
NameStatus
sdf1
zxc2
xcv1
cvb1
fgh2

 

 

Now, I want to find duplicate data by Name based on table 02, and then delete them. I mean that the SAS searchs tables to find data with same name and delete them just from table 01, like the table below:

 

Final table
DateTimeNamePrice
11asd1
12asd2
21dfg1
31asd3

 

 

All variable except Name are numeric.

 

1. How can I do that?

2. How can I report on deleted data?

 

Thanks in advance.


Accepted Solutions
Solution
‎07-28-2017 05:40 AM
SAS Employee
Posts: 5

Re: How to delete duplicate data based on matching data in two table?

[ Edited ]
Posted in reply to aminkarimid

You could use proc sql:

 

proc sql;
	create table table01 as
	select * from table01
	where name not in (select name from table02);
quit;

 If you'd like to report on what rows would be deleted, you could just run the inversed query beforehand:

 

 

proc sql;
	create table table01 as
	select * from table01
	where name in (select name from table02);
quit;

 

 

View solution in original post


All Replies
Super User
Posts: 7,863

Re: How to delete duplicate data based on matching data in two table?

Posted in reply to aminkarimid

Do a join on name:

proc sort data=have1;
by name;
run;

proc sort data=have2;
by name;
run;

data
  want
  deleted
;
merge
  have1 (in=a)
  have2 (in=b keep=name);
;
by name;
if a;
if b
then output deleted;
else output want;
run;

Or (if the size of data allows) create a format from have2 that renders a 1 for each existing name, and 0 otherwise. Then you can avoid sorting of have1.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎07-28-2017 05:40 AM
SAS Employee
Posts: 5

Re: How to delete duplicate data based on matching data in two table?

[ Edited ]
Posted in reply to aminkarimid

You could use proc sql:

 

proc sql;
	create table table01 as
	select * from table01
	where name not in (select name from table02);
quit;

 If you'd like to report on what rows would be deleted, you could just run the inversed query beforehand:

 

 

proc sql;
	create table table01 as
	select * from table01
	where name in (select name from table02);
quit;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 168 views
  • 0 likes
  • 3 in conversation