BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Hello_there
Lapis Lazuli | Level 10

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:

Hello_there_1-1691160809394.png

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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:

Hello_there_1-1691160809394.png

 

Thanks


 

View solution in original post

6 REPLIES 6
ballardw
Super User

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:

Hello_there_1-1691160809394.png

 

Thanks


 

Hello_there
Lapis Lazuli | Level 10
Thanks, this worked out great
Tom
Super User Tom
Super User

@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
Lapis Lazuli | Level 10
Thanks for making me aware of this.
sbxkoenk
SAS Super FREQ

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
Opal | Level 21

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1103 views
  • 8 likes
  • 5 in conversation