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

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

aminkarimid — Fri, 28 Jul 2017 06:22:31 GMT

Hello everybody,

I have two tables which are shown below:

Table 01
Date	Time	Name	Price
1	1	asd	1
1	2	asd	2
1	2	sdf	1
1	4	sdf	2
2	1	dfg	1
2	2	sdf	2
3	1	asd	3
3	2	fgh	1
3	3	fgh	2

Table 02
Name	Status
sdf	1
zxc	2
xcv	1
cvb	1
fgh	2

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
Date	Time	Name	Price
1	1	asd	1
1	2	asd	2
2	1	dfg	1
3	1	asd	3

All variable except Name are numeric.

1. How can I do that?

2. How can I report on deleted data?

Thanks in advance.

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

Kurt_Bremser — Fri, 28 Jul 2017 06:47:02 GMT

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.

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

WesleyD — Fri, 28 Jul 2017 08:41:17 GMT

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;