BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Dear,

In my data some values contain "!!" characters in the middle of values. I need to remove the characters and the values after the characters. Thank you

 

data

term

Subject did not drink 50% of the meal!! Chicken

 

output needed;

Subject did not drink 50% of the meal

10 REPLIES 10
knveraraju91
Barite | Level 11

Dear,

In my data some values contain "!!" characters in the middle of values. I need to remove the characters and the values after the characters. Thank you

 

data

term

Subject did not drink 50% of the meal!! Chicken

 

output needed;

Subject did not drink 50% of the meal

Kurt_Bremser
Super User

I'd try the tranwrd() function, and then scan():

data have;
infile cards truncover;
input term $100.;
cards;
Subject did not drink 50% of the meal!! Chicken
;
run;

data want;
set have;
term = tranwrd(term,"!!","|");
term = scan(term,1,"|");
run;

 Since one can only use single characters as delimiters in scan().

PeterClemmensen
Tourmaline | Level 20

Try this

 

data test;
string='Subject did not drink 50% of the meal!! Chicken';
newstring = substr( string,1,index(string,'!!') - 1);
run;
Patrick
Opal | Level 21

If you just want everything before the first exclamation mark then the scan() function on its own should do.

Not sure why @Kurt_Bremser believes that duplicates need replacement for your use case.

data have;
  infile cards truncover;
  input term $100.;
  cards;
Subject did not drink 50% of the meal!! Chicken
;
run;

data want;
  set have;
  term = scan(term,1,"!");
run;
Patrick
Opal | Level 21

@Kurt_Bremser

But didn't the OP tell us that he just wants "everything" from the beginning up to the first exclamation mark? So what do we care about any futher exclamation marks in the string. Based on your code I must be missing something.

Kurt_Bremser
Super User

Look at the log from this:

data _null_;
term = 'Subject complains! Subject did not drink 50% of the meal!! Chicken';
term1 = tranwrd(term,"!!","|");
term1 = scan(term,1,"|");
term2 = scan(term,1,"!");
put term1=;
put term2=;
run;

The OP stated that the delimiter is a sequence of two exclamation marks.

Granted that I may be a little overcautious here, but better safe than sorry.

Patrick
Opal | Level 21

@Kurt_Bremser

Thanks. I did miss that the delimiter is TWO pipes. That explains what your'e doing.

 

Here a variant using RegEx

data have;
  term = 'Subject complains!! Subject did not drink 50% of the meal!! Chicken';
  output;
  term = 'Subject complains! Subject did not drink 50% of the meal!! Chicken';
  output;
  term = 'something';
  output;
run;

data want;
  set have;
  if 0 then want_str=term;
  want_str=prxchange('s/(!{2}.*)//oi',1,term);
run;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sounds like a job for the compress function - which removes given characters:

data have;
  infile cards truncover;
  input term $100.;
  term2=compress(term,"!");
cards;
Subject did not drink 50% of the meal!! Chicken
;
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!

How to Concatenate Values

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.

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
  • 10 replies
  • 2374 views
  • 9 likes
  • 6 in conversation