Help using Base SAS procedures

delete records depending of next record condition

Accepted Solution Solved
Reply
Super Contributor
Posts: 301
Accepted Solution

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.

Thanks in advance.

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
Respected Advisor
Posts: 3,799

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

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,

Jagadish

Thanks,
Jag
Super Contributor
Posts: 301

Re: delete records depending of next record condition

Posted in reply to Jagadishkatam

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.

Solution
‎10-16-2013 08:39 AM
Respected Advisor
Posts: 3,799

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

Posted in reply to data_null__

Brilliant data_null_ .It works. Thanks.

Frequent Contributor
Posts: 87

Re: delete records depending of next record condition

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

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

Posted in reply to data_null__

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

Posted in reply to data_null__


@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: 7,467

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.

Respected Advisor
Posts: 3,156

Re: delete records depending of next record condition

Posted in reply to data_null__

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 Smiley Happy

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.

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

Discussion stats
  • 17 replies
  • 555 views
  • 3 likes
  • 7 in conversation