topic Replacing a character with a blank in a string in SAS Enterprise Guide

Replacing a character with a blank in a string

cnilsen — Fri, 22 Feb 2019 20:43:21 GMT

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!

Re: Replacing a character with a blank in a string

novinosrin — Fri, 22 Feb 2019 20:45:46 GMT

Please post a sample of your input and the expect output

Re: Replacing a character with a blank in a string

PaigeMiller — Fri, 22 Feb 2019 20:50:49 GMT

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=p05ww22zp7lcg3n1bjk7v93tscyo.htm&locale=en

Re: Replacing a character with a blank in a string

cnilsen — Fri, 22 Feb 2019 20:55:56 GMT

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.

Re: Replacing a character with a blank in a string

ChrisHemedinger — Fri, 22 Feb 2019 20:55:57 GMT

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;

Re: Replacing a character with a blank in a string

cnilsen — Fri, 22 Feb 2019 20:58:05 GMT

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

Re: Replacing a character with a blank in a string

PaigeMiller — Fri, 22 Feb 2019 20:58:41 GMT

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.