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.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.