How to remove a pair of rows based on condition

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to remove a pair of rows based on condition

I want to only keep the pair of observation with value F and M in 'a' if value in variable 'b' are both 1. Delete any pair as long as one of them has value 0 in 'b'.

Thanks for helping!

data test;

input a $ b;

datalines;

F 0

M 1

F 0

M 0

F 1

M 1

F 1

M 0

;

run;


Accepted Solutions
Solution
‎04-17-2012 08:07 PM
Super Contributor
Posts: 1,636

Re: How to remove a pair of rows based on condition

try this one:

data test;

input a $ b;

datalines;

F 0

M 1

F 0

M 0

F 1

M 1

F 1

M 0

;

run;

data temp;

  set test;

  if a='F' then group+1;

  if a='M' and (b+lag(b))=2 then flag=1;

proc sql;

   create table want as

     select a,b from temp

    where group in (select distinct group from temp where flag=1);

  quit;

proc print;

run;

Linlin

View solution in original post


All Replies
Solution
‎04-17-2012 08:07 PM
Super Contributor
Posts: 1,636

Re: How to remove a pair of rows based on condition

try this one:

data test;

input a $ b;

datalines;

F 0

M 1

F 0

M 0

F 1

M 1

F 1

M 0

;

run;

data temp;

  set test;

  if a='F' then group+1;

  if a='M' and (b+lag(b))=2 then flag=1;

proc sql;

   create table want as

     select a,b from temp

    where group in (select distinct group from temp where flag=1);

  quit;

proc print;

run;

Linlin

Contributor
Posts: 40

Re: How to remove a pair of rows based on condition

Clever! Thank you so much.

Z

Respected Advisor
Posts: 4,920

Re: How to remove a pair of rows based on condition

Assuming there is more to data test than presented Smiley Happy

data test;
input a $ b;
datalines;
F 0
M 1
F 0
M 0
F 1
M 1
F 1
M 0
;

data testv / view=testv;
set test;
_id + a = "F";
run;

proc sql;
create table want (drop=_id) as
select * from testv
group by _id
having min(b)=1 and count(b)=2
order by _id, a;

PG

PG
Contributor
Posts: 40

Re: How to remove a pair of rows based on condition

You are great!

What does "data testv / view=testv" mean?

And what does _id+a="F" mean?

Z

Respected Advisor
Posts: 4,920

Re: How to remove a pair of rows based on condition

One sleep later, I found an even simpler way (inspired by fellow forum wizzes) :

data test;

input a $ b;

datalines;

F 0

M 1

F 0

M 0

F 1

M 1

F 1

M 0

;

data want;

array OK{1} _temporary_; /* not kept, no conflict with input variable names */

set test(firstobs=2);

OK{1} = a="M" and lag(a)="F" and b and lag(b);

set test;

if OK{1} then output;

set test(firstobs=2);

if OK{1} then output;

run;

To answer your questions:

data testv / view=testv; creates a data view, not a data set. The data is not stored, just the way to generate it.

_id+a="F"; adds the value of a="F" to _id. The value of a="F" is 1 if true, 0 otherwise.

PG

PG
Super Contributor
Posts: 1,636

Re: How to remove a pair of rows based on condition

It maybe easier to understand if changing

_id+a="F";

to

_id+(a="F");

Respected Advisor
Posts: 3,156

Re: How to remove a pair of rows based on condition

How about this one, seems even simpler:

data have;

input a $ b;

id=_n_;

datalines;

F 0

M 1

F 0

M 0

F 1

M 1

F 1

M 0

;

data want (drop=_Smiley Happy;

set have;

set have (firstobs=2 keep=b rename=b=_b)

  have (obs=1 drop=_all_);

if _f=2 then output;else _f=0;

if b*_b*(a='F')=1 then do;  output;_f=1;end;

_f+1;

run;

proc print;run;

Haikuo

Contributor
Posts: 40

Re: How to remove a pair of rows based on condition

Elegant data step codes.

Respected Advisor
Posts: 3,156

Re: How to remove a pair of rows based on condition

One technique used in the code was from the classic 'look back and look ahead', you can actually use 'merge' instead of 'set' to make it even shorter:

data want (drop=_Smiley Happy;

merge have have (firstobs=2 keep=b rename=b=_b);

if _f=2 then output;else _f=0;

if b*_b*(a='F')=1 then do; output;_f=1;end;

_f+1;

run;

Haikuo

Contributor
Posts: 40

Re: How to remove a pair of rows based on condition

Very nice!

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 536 views
  • 13 likes
  • 4 in conversation