Help using Base SAS procedures

Delete Multiple Rows If one row meets criteria

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

Delete Multiple Rows If one row meets criteria


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


Accepted Solutions
Solution
‎05-16-2013 03:23 PM
Super User
Posts: 5,509

Re: Delete Multiple Rows If one row meets criteria

Posted in reply to omega1983

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.

View solution in original post


All Replies
Super Contributor
Posts: 334

Re: Delete Multiple Rows If one row meets criteria

Posted in reply to omega1983

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!

EJ

Solution
‎05-16-2013 03:23 PM
Super User
Posts: 5,509

Re: Delete Multiple Rows If one row meets criteria

Posted in reply to omega1983

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.

Occasional Contributor
Posts: 15

Re: Delete Multiple Rows If one row meets criteria

Posted in reply to omega1983

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;

Respected Advisor
Posts: 3,156

Re: Delete Multiple Rows If one row meets criteria

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

Occasional Contributor
Posts: 7

Re: Delete Multiple Rows If one row meets criteria

Posted in reply to omega1983

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?

Super User
Posts: 5,509

Re: Delete Multiple Rows If one row meets criteria

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

Occasional Contributor
Posts: 7

Re: Delete Multiple Rows If one row meets criteria

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 3056 views
  • 4 likes
  • 6 in conversation