Hello, can you help me get the following output? Basically I want to remove any values in "()" without using substr function.
current data:
Influenza Invalid,FLU-22 Invalid |
Influenza Invalid(20),FLU-22 Invalid(2999) |
Influenza Canceled(77),FLU-22 Canceled(77) |
Influenza Negative,FLU-22 Positive |
want data:
Influenza Invalid,FLU-22 Invalid |
Influenza Invalid,FLU-22 Invalid |
Influenza Canceled,FLU-22 Canceled |
Influenza Negative,FLU-22 Positive |
Can you show the code you have tried? Seems like maybe this would be a good a better task for regular expressions (PRXCHANGE). To use SCAN(), I think you'd have to use a DO-loop, assuming you don't know how many sets of parentheses you might have in a value.
As Quentin suggested, regex replace will do it easily.
data have;
infile cards;
input text &:$50.;
cards;
Influenza Invalid,FLU-22 Invalid
Influenza Invalid(20),FLU-22 Invalid(2999)
Influenza Canceled(77),FLU-22 Canceled(77)
Influenza Negative,FLU-22 Positive
;
run;
data want;
set have;
* lazily search for matching pair of parentheses and replace with empty string;
text_clean = prxchange('s/\(.*?\)//', -1, text);
run;
I suspect that it will be better to use regular expressions instead, but you asked for SCAN() function so here you go.
data want;
set have;
length string2 $80.;
do i=1 by 2 to countw(string,'()');
string2=cats(string2,scan(string,i,'()'));
end;
drop i;
run;
Results
Obs string string2 1 Influenza Invalid,FLU-22 Invalid Influenza Invalid,FLU-22 Invalid 2 Influenza Invalid(20),FLU-22 Invalid(2999) Influenza Invalid,FLU-22 Invalid 3 Influenza Canceled(77),FLU-22 Canceled(77) Influenza Canceled,FLU-22 Canceled 4 Influenza Negative,FLU-22 Positive Influenza Negative,FLU-22 Positive
Tom,
I think PRX is the best choice.
If data like the following,your code does not work !
data have;
infile cards;
input string &:$50.;
cards;
Influenza Invalid,FLU-22 Invalid
Influenza Invalid(20),FLU-22 Invalid(2999)
Influenza Canceled(77),FLU-22 Canceled(77)
Influenza Negative,FLU-22 Positive
(Influenza)
;
run;
data want;
set have;
length string2 string3 $80.;
do i=1 by 2 to countw(string,'()');
string2=cats(string2,scan(string,i,'()'));
end;
string3 = prxchange('s/\(.*?\)//', -1, string);
drop i;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.