Replacing a character with a blank in a string

[cnilsen]

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!

[novinosrin]

Please post a sample of your input and the expect output

[PaigeMiller]

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...

[cnilsen]

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. 

[PaigeMiller]

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.

[ChrisHemedinger · Accepted solution]

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;

[cnilsen]

awesome... and very simple. I appreciate the quick responses from everyone. Thanks!