BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cnilsen
Quartz | Level 8

HI All,

 

I am a novice programmer and facing a new programming challenge. 

 

I'm working with enterprise guide to create a data set to be exported ultimately..  (I estimate about 1.7 million records..)

 

A character column within it contains periods (.) in some of the rows. (the value is not always found, or in the same position within the string) I'd like to scan the column and replace them if possible with a blank. I am not sure if the is a function that I can use with creating a calculated column OR, how I can create a data set, then run a proc statement against it to read the rows, scan/replace if found the value and update the data.

 

I have tried searching the community for answers, but was unsuccessful. 

Any help would be appreciated!

 

 

- Chris N.
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

As @PaigeMiller said, TRANSLATE does the trick.

 

PROC SQL;
   CREATE TABLE WORK.table AS 
   SELECT /* newval */
            (TRANSLATE(t1.OriginalCol, ' ', '.')) AS newval
      FROM WORK.original t1;
QUIT;
SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

Please post a sample of your input and the expect output

PaigeMiller
Diamond | Level 26

Yes, showing us an example of your data would definitely help get a quicker answer.

 

However, the TRANSLATE function in SAS will turn dots into blanks. There are examples in the documentation.

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p...

--
Paige Miller
cnilsen
Quartz | Level 8

a sample of text might be :

 

"MEXICAN RED. ROSES - BOUQUET."

 

I'd like the result to be:

"MEXICAN RED ROSES - BOUQUET"

 

since this column was manually keyed by someone.. it is not standardized in any way.

Typos and excessive punctuation are common with the column data. 

- Chris N.
PaigeMiller
Diamond | Level 26

Just a minor point here, but this example you show does not turn dots into blanks. It removes the dots from the text string, and does not put a blank in there at all. So which do you want? You might also want to look at the COMPRESS function.

--
Paige Miller
ChrisHemedinger
Community Manager

As @PaigeMiller said, TRANSLATE does the trick.

 

PROC SQL;
   CREATE TABLE WORK.table AS 
   SELECT /* newval */
            (TRANSLATE(t1.OriginalCol, ' ', '.')) AS newval
      FROM WORK.original t1;
QUIT;
SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!
cnilsen
Quartz | Level 8
awesome... and very simple. I appreciate the quick responses from everyone. Thanks!
- Chris N.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

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
  • 29596 views
  • 7 likes
  • 4 in conversation