topic Re: Delete matched observations in New SAS User

Delete matched observations

dapenDaniel — Sat, 15 Dec 2018 22:51:52 GMT

Hello. I have two data sets. In the Dataset A, an ID has multiple observations. Dataset B is a list of ID that I need to delete from Dataset A. The following is an example.

Dataset A Dataset B

ID Observations ID

aa a11 aa

aa a12 bb

aa a13

bb b12

bb b22

cc c45

cc c56

I want to delete the observations which have the same ID with dataset B from Dataset A. What code do I need? Thanks.

Re: Delete matched observations

PGStats — Sat, 15 Dec 2018 23:33:08 GMT

proc sql;
delete from A
where id in (select id from B);
quit;

Re: Delete matched observations

novinosrin — Sun, 16 Dec 2018 01:01:06 GMT

Don't pay much attention to this. I'm trying to learn indexes, that's all


data a;
input id;
cards;
1
2
2
3
3
3
;

data b;
input id;
cards;
3
;


proc sql;
create index id on a (id);
quit;

data a; 
set b; 
do until (_iorc_=%sysrc(_dsenom));
modify a key=id; 
select (_iorc_); 
when(%sysrc(_sok)) do; /* A match was found, update master */
remove;
end;
when (%sysrc(_dsenom)) do; /* No match was found */
_error_ = 0;
end; 
otherwise do; 
length errormessage $200.; 
errormessage = iorcmsg();
put "ATTENTION: unknown error condition: " errormessage;
end;
end;
end;
run;

Re: Delete matched observations

Reeza — Sun, 16 Dec 2018 02:16:09 GMT

proc sql;
create table want as
select *
from tableA
where ID not in (select * from tableB);
quit;

@dapenDaniel wrote:

Hello. I have two data sets. In the Dataset A, an ID has multiple observations. Dataset B is a list of ID that I need to delete from Dataset A. The following is an example.

Dataset A Dataset B

ID Observations ID

aa a11 aa

aa a12 bb

aa a13

bb b12

bb b22

cc c45

cc c56

I want to delete the observations which have the same ID with dataset B from Dataset A. What code do I need? Thanks.

Re: Delete matched observations

Jagadishkatam — Sun, 16 Dec 2018 10:21:14 GMT

Alternatively in a data step by merge statement

data want;
merge A(in=a) B(in=b);
by id;
if a and not b;
run;