topic Re: How do i strip out specific characters from this data set variable? in SAS Programming

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

Hello_there — Fri, 04 Aug 2023 14:53:56 GMT

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

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

ballardw — Fri, 04 Aug 2023 15:02:41 GMT

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

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

Hello_there — Fri, 04 Aug 2023 15:04:08 GMT

Thanks, this worked out great

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

Tom — Fri, 04 Aug 2023 15:22:45 GMT

@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(' '));

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

Hello_there — Fri, 04 Aug 2023 15:24:23 GMT

Thanks for making me aware of this.

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

sbxkoenk — Fri, 04 Aug 2023 21:08:39 GMT

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

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

Patrick — Fri, 04 Aug 2023 23:41:08 GMT

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;