How do i strip out specific characters from this data set variable?

[Hello_there]

Hello,

This is a data manipulation practice exercise.

How do I strip out all instances of "UNK", "000", "00" efficiently?

data have;
length date $12;
	input date $;
datalines;
2017-02-23
2017-02-UNK
2017-UNK-UNK
UNK-UNK-UNK
2017-02-00
2017-00-00
0000-00-00
;
run;

Want:

Thanks

[ballardw · Accepted solution]

One way:

data have;
length date $12;
	input date $;
   date=tranwrd(date,'0000','');
   date=tranwrd(date,'-00','');
   date=tranwrd(date,'-UNK','');
   date=tranwrd(date,'UNK','');
datalines;
2017-02-23
2017-02-UNK
2017-UNK-UNK
UNK-UNK-UNK
2017-02-00
2017-00-00
0000-00-00
;
run;

---

@Hello_there wrote:

Hello,

This is a data manipulation practice exercise.

How do I strip out all instances of "UNK", "000", "00" efficiently?

data have;
length date $12;
	input date $;
datalines;
2017-02-23
2017-02-UNK
2017-UNK-UNK
UNK-UNK-UNK
2017-02-00
2017-00-00
0000-00-00
;
run;

Want:

Thanks

---

[Hello_there]

Thanks, this worked out great

[Tom]

---

@Hello_there wrote:
Thanks, this worked out great

---

One thing to remember is that TRANWRD() does not totally remove the strings. In stead it is replacing them with a single space. If you need to totally remove the strings you need to use TRANSTRN() as that can actually replace the target string with nothing.  But to generate the nothing you need to use TRIMN() function.

Why?

SAS stores character strings as fixed length that is padded with spaces.  The shortest possible character variable is 1 character.  So even if you type '' in your code TRANWRD() is still going to see ' ' as the text to be substituted.  Similarly if you use TRIM() to remove trailing spaces you still end up with a string that is at least one character long.

But when SAS introduced TRIMN() and TRANSTRN() functions they allowed them to generate and receive strings of length zero.

date=transtrn(date,'-UNK',trimn(' '));

[Hello_there]

Thanks for making me aware of this.

[sbxkoenk]

Deleting a substring from a SAS string
By Leonid Batkhan on SAS Users February 22, 2021
https://blogs.sas.com/content/sgf/2021/02/22/deleting-a-substring-from-a-sas-string/

Inserting a substring into a SAS string
By Leonid Batkhan on SAS Users February 15, 2021
https://blogs.sas.com/content/sgf/2021/02/15/inserting-a-substring-into-a-sas-string/

Koen

[Patrick]

Here a RegEx approach.

data have;
  length date $12;
  input date $;
  date=prxchange('s/-?(unk|00+)-?//oi',-1,strip(date));
  datalines;
2017-02-23
2017-02-UNK
2017-UNK-UNK
UNK-UNK-UNK
2017-02-00
2017-00-00
0000-00-00
;
run;