Solved
Contributor
Posts: 40

# 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

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

Posts: 5,540

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

Assuming there is more to data test than presented

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

Posts: 5,540

## 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;

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");

Posts: 3,167

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

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=_;

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.

Posts: 3,167

## 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=_;

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.