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,777

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,128

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

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,777

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;

Respected Advisor
Posts: 3,777

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: 7,356

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,124

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 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.

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

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