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
This assumption with the method is that there are no missing subrows.
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
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.
This assumption with the method is that there are no missing subrows.
Brilliant data_null_ .It works. Thanks.
@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;
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.
@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?
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
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;
has to be "or" rather than "and" and it works
ups, the first run need to be removed , and the code will be OK.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.