BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michtka
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

17 REPLIES 17
Jagadishkatam
Amethyst | Level 16

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
michtka
Fluorite | Level 6

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.

data_null__
Jade | Level 19

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;
michtka
Fluorite | Level 6

Brilliant data_null_ .It works. Thanks.

helloSAS
Obsidian | Level 7

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

data_null__
Jade | Level 19

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.

michtka
Fluorite | Level 6

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.

helloSAS
Obsidian | Level 7


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


art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

michtka
Fluorite | Level 6

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;

michtka
Fluorite | Level 6

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

michtka
Fluorite | Level 6

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

pradeepalankar
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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