## delete records depending of next record condition

Solved
Super Contributor
Posts: 301

# delete records depending of next record condition

Dear all,

how I can delete the records with cat ="rowx", x=1-3 when the variable color in the next record is blank?

Only want to keep the records cat="rowx" when the variable color in the next record  is populated....please see output* I want to achieve.

V.

data new;

input cat \$1-7 color \$ 9-17;

datalines;

row1

subrow1 green

row1

subrow2

row1

subrow3 red

row2

subrow1 red

row2

subrow2

row3

subrow1

row3

subrow2 orange

;

run;

data want;

input cat \$1-7 color \$9-17;

datalines;

row1

subrow1 green

row1

subrow3 red

row2

subrow1 red

row3

subrow2 orange

;

run;

proc print data=want noobs;

run;

*output:

cat        color

row1

subrow1    green

row1

subrow3    red

row2

subrow1    red

row3

subrow2    orange

Accepted Solutions
Solution
‎10-16-2013 08:39 AM
Posts: 3,852

## Re: delete records depending of next record condition

This assumption with the method is that there are no missing subrows.

data new;
input cat \$1-7 color \$ 9-17;
datalines;
row1
subrow1 green
row1
subrow2
row1
subrow3 red
row2
subrow1 red
row2
subrow2
row3
subrow1
row3
subrow2 orange
;;;;
run;
proc print;

run;

data new;
modify new;
set new(firstobs=2 keep=color rename=(color=_color));
if cat eq: 'sub' then _color=color;
if missing(_color) then remove;
run;
proc print data=new;
run;

All Replies
Posts: 1,147

## Re: delete records depending of next record condition

Hi,

Please try the below code, i tried to control the reading of the data at the input statement and created a new variable called subcat, then we get  unique records for the data and then which ever observations are blank for color variable, they are deleted.

data new;

input cat \$1-7 / subcat \$1-7 color \$ 9-17;

if color='' then delete;

datalines;

row1

subrow1 green

row1

subrow2

row1

subrow3 red

row2

subrow1 red

row2

subrow2

row3

subrow1

row3

subrow2 orange

;

run;

Thanks,

Thanks,
Jag
Super Contributor
Posts: 301

## Re: delete records depending of next record condition

but I am interested in not modify the input statement, because my real problem is a dataset with many records.

It was just a little example that I want to achieve:

the challenge here is to remove the records CAT with "rowx" when the next record in the variable COLOR is not populated.

Solution
‎10-16-2013 08:39 AM
Posts: 3,852

## Re: delete records depending of next record condition

This assumption with the method is that there are no missing subrows.

data new;
input cat \$1-7 color \$ 9-17;
datalines;
row1
subrow1 green
row1
subrow2
row1
subrow3 red
row2
subrow1 red
row2
subrow2
row3
subrow1
row3
subrow2 orange
;;;;
run;
proc print;

run;

data new;
modify new;
set new(firstobs=2 keep=color rename=(color=_color));
if cat eq: 'sub' then _color=color;
if missing(_color) then remove;
run;
proc print data=new;
run;
Super Contributor
Posts: 301

## Re: delete records depending of next record condition

Brilliant data_null_ .It works. Thanks.

Frequent Contributor
Posts: 87

## Re: delete records depending of next record condition

@data _null_

can you please explain how this code works?

data new;
modify new;
set new(firstobs=2 keep=color rename=(color=_color));
if cat eq: 'sub' then _color=color;
if missing(_color) then remove;
run;

Posts: 3,852

## Re: delete records depending of next record condition

It just reads each obs from NEW (modify) and the next COLOR as _COLOR from NEW (SET firstobs=2).  If the next color is missing the record is removed.  shows how the same can be written using MERGE.

Super Contributor
Posts: 301

## Re: delete records depending of next record condition

very clever data_null_, that firstobs=2 was the key.

Thank you Haikuo for show the alternative with merge, to me it is more familiar way that

that using modify (it his high class programming to me :smileylaugh: ).

Thanks again.

Frequent Contributor
Posts: 87

## Re: delete records depending of next record condition

@data _null_

thanks for the explanation.

When you say if cat eq: 'sub' then _color=color;  is it looking at sub from modify NEW or set NEW?

PROC Star
Posts: 8,163

## Re: delete records depending of next record condition

: You're not reading what the code is showing.  The only variable coming in from set new is _color.

Posts: 3,167

## Re: delete records depending of next record condition

Nice! This would be the cleanest way of doing it. Similarly one can do the same thing using Merge or Set only, here is the merge example:

data want;

merge new new(firstobs=2 keep=color rename=(color=_color));

if cat eq: 'sub' then _color=color;

if not missing(_color) then output;

drop _:;

run;

Haikuo

Super Contributor
Posts: 301

## Re: delete records depending of next record condition

Hi guys, Hai.Kuo ,data_null_ ,  in terms of extend the problem, I  have got the next problem.

I would like to remove the two rowx  when the structure dont have color in the 3rd row:

rowx

rowx

blank

and keep the two rowx when  the structure have color in the 3rd row:

row1

row1

blank  color

I just wrote a pice of code ***, but it doesnt work at the moment, what I am missing?  Thanks in advance. V.

****input dataset;

data new;

input cat \$1-7 color \$ 8-17;

datalines;

row1

row1

green

row1

row1

row1

row1

red

row2

row2

red

row2

row2

row3

row3

row3

row3

orange

;

run;

******

Code:

data want;

merge new new(firstobs=2 keep=color rename=(color=_color)) new(firstobs=3 keep=color rename=(color=_color2)) ;

run;

if cat eq: ' ' then do;_color=color;_color2=_color;end;

if not missing(_color) and not missing (_color2) then output;

drop _:;

run;

Super Contributor
Posts: 301

## Re: delete records depending of next record condition

has to be  "or" rather than "and" and it works

Super Contributor
Posts: 301

## Re: delete records depending of next record condition

ups, the first run need to be removed , and the code will be OK.

Frequent Contributor
Posts: 106

## Re: delete records depending of next record condition

data new;

input cat \$1-7 color \$ 9-17;

datalines;

row1

subrow1 green

row1

subrow2

row1

subrow3 red

row2

subrow1 red

row2

subrow2

row3

subrow1

row3

subrow2 orange

;

run;

data new_1;

set new;

rename color = new_color;

cat = lag( cat );

if cat ^= '';

run;

data new(keep=cat color);

merge new new_1;

if color ='' and new_color='' then delete;

run;

🔒 This topic is solved and locked.