topic Re: delete records depending of next record condition in SAS Procedures

delete records depending of next record condition

michtka — Wed, 16 Oct 2013 11:13:41 GMT

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.

Thanks in advance.

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

Re: delete records depending of next record condition

Jagadishkatam — Wed, 16 Oct 2013 12:29:41 GMT

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,

Jagadish

Re: delete records depending of next record condition

data_null__ — Wed, 16 Oct 2013 12:39:30 GMT

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; 

Re: delete records depending of next record condition

michtka — Wed, 16 Oct 2013 12:40:31 GMT

Thank you for this Jagadishkatam,

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.

Re: delete records depending of next record condition

michtka — Wed, 16 Oct 2013 12:45:01 GMT

Brilliant data_null_ .It works. Thanks.

Re: delete records depending of next record condition

pradeepalankar — Wed, 16 Oct 2013 13:11:57 GMT

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;

Re: delete records depending of next record condition

michtka — Wed, 16 Oct 2013 14:08:19 GMT

sorry guys...but still it doesn't work in my real problem, need to thinking about it

Re: delete records depending of next record condition

Jagadishkatam — Wed, 16 Oct 2013 14:22:54 GMT

The answer provided by data _null_ was perfect we believe, could you please let us know what is the problem you are facing.

Re: delete records depending of next record condition

michtka — Wed, 16 Oct 2013 14:50:40 GMT

My apologize data_null_ , it works fine in my problem.

Thank you everyone for the answers.

Re: delete records depending of next record condition

helloSAS — Wed, 16 Oct 2013 18:14:30 GMT

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

Re: delete records depending of next record condition

Haikuo — Wed, 16 Oct 2013 18:37:43 GMT

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

Re: delete records depending of next record condition

data_null__ — Wed, 16 Oct 2013 19:22:39 GMT

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.

Re: delete records depending of next record condition

michtka — Wed, 16 Oct 2013 21:08:41 GMT

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.

Re: delete records depending of next record condition

helloSAS — Thu, 17 Oct 2013 01:06:33 GMT

@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?

Re: delete records depending of next record condition

art297 — Thu, 17 Oct 2013 02:38:07 GMT

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

Re: delete records depending of next record condition

michtka — Sat, 19 Oct 2013 01:21:24 GMT

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

blank

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

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

green

row1

red

row2

red

row2

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;

Re: delete records depending of next record condition

michtka — Sat, 19 Oct 2013 01:31:40 GMT

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

Re: delete records depending of next record condition

michtka — Sat, 19 Oct 2013 01:33:00 GMT

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