topic Re: Delete Multiple Rows If one row meets criteria in SAS Procedures

Delete Multiple Rows If one row meets criteria

omega1983 — Thu, 16 May 2013 19:02:08 GMT

ID Log_Code

11 A

12 A

12 B

13 A

14 A

14 B

I want to eliminate both rows that meet the criteria of having 'B' as a log code. So in this case I want to eliminate ID 12 and 14. How can I do this in a SAS datastep or proc sql

Re: Delete Multiple Rows If one row meets criteria

esjackso — Thu, 16 May 2013 19:08:35 GMT

Maybe Im missing something but you should be able to just use a where statement in either case:

Data new;

set old;

where log_code ne "B";

run;

proc sql;

select *

from old

where log_code ne "B"

;

quit;

If the varying padded log_codes are how the data appears then you might have to use strip(log_code) to remove leading and trailing spaces.

Hope this helps!

Re: Delete Multiple Rows If one row meets criteria

Astounding — Thu, 16 May 2013 19:23:51 GMT

There are a few ways, but this is probably the most intuitive. It assumes your data are sorted by ID:

data want;

merge have

have (keep=id log_code where=(log_code='B') in=drop_these);

by id;

if drop_these then delete;

run;

It is conceivable you will need to ignore messages indicating a many-to-many merge.

The SQL coders here can show you a way that may be just as intuitive to you ... depends on whether SQL or DATA steps are a stronger area for you.

Good luck.

Re: Delete Multiple Rows If one row meets criteria

sascom10 — Thu, 16 May 2013 20:01:22 GMT

SQL solution -

data have;
input id $3. log_code $3.;
datalines;
11 A
12 A
12 B
13 A
14 A
14 B
run;

proc sql;
create table want as
    select *
   from have
where id not in (select distinct id
     from have
       where log_code in ('B'));
quit;

Re: Delete Multiple Rows If one row meets criteria

aland1 — Thu, 16 May 2013 21:35:12 GMT

Like EJ, I'm having trouble understanding what needs done here. I know it's been answered multiple times now, but out of curiosity, I'd like to understand what's being asked here exactly.

As far as I can tell, we simply want to exclude anything with log_code = 'B'? Correct? Is that the case?

Re: Delete Multiple Rows If one row meets criteria

Astounding — Thu, 16 May 2013 21:36:58 GMT

No, it's more complex. If log_code='B', delete all observations for that ID, including those that have log_code='A'.

Re: Delete Multiple Rows If one row meets criteria

aland1 — Thu, 16 May 2013 21:40:08 GMT

Ok. I see. Now it makes sense. Thanks.

Re: Delete Multiple Rows If one row meets criteria

Haikuo — Fri, 17 May 2013 01:04:22 GMT

You can also simply do:

proc sql;

create table want as

select * from have group by id having sum(log_code='B') <1; quit;

Haikuo