Fluorite | Level 6

How to remove dots from numerics

Hello Everybody

I have a problem, I have a DB for fifteen thousand lines. And this table has a specific column, which I call "CPF".

Well, in this CPF I have this type of sequence of numbers like this: "086.602.38373" "838.399.200.13"

I want to remove these points, so: "08660238373" "83839920013"

My table name: Gus If someone can write the entire code by pulling my CPF column from my Gus table.

Best regards,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: How to remove dots from numerics

You cannot have values like  "086.602.38373" "838.399.200.13" in a numeric variable.  So your source variable must be a character string.  You can remove the periods from a character variable using the COMPRESS() function.  If you want to convert the string without periods into a number using the INPUT() function. Note that SAS stores all numbers as floating point so the maximum number of digits you have have is 16 (really 15).

``````data gus;
infile CPF \$20. ;
cards;
086.602.38373
838.399.200.13
;

data want;
set gus;
CPF_number = input(compress(CPF,'.'),32.);
format CPF_number 16.;
run;``````
14 REPLIES 14
Tourmaline | Level 20

Re: How to remove dots from numerics

This is a character variable, correct?

Fluorite | Level 6

Super User

Re: How to remove dots from numerics

Are you using an English language or a different language?

A numeric variable with the BEST format applied would not show up like that.
Super User

Re: How to remove dots from numerics

You cannot have values like  "086.602.38373" "838.399.200.13" in a numeric variable.  So your source variable must be a character string.  You can remove the periods from a character variable using the COMPRESS() function.  If you want to convert the string without periods into a number using the INPUT() function. Note that SAS stores all numbers as floating point so the maximum number of digits you have have is 16 (really 15).

``````data gus;
infile CPF \$20. ;
cards;
086.602.38373
838.399.200.13
;

data want;
set gus;
CPF_number = input(compress(CPF,'.'),32.);
format CPF_number 16.;
run;``````
Fluorite | Level 6

Re: How to remove dots from numerics

data cpf;
infile cpf \$20. ;
cards;
838.399.200.13
;
data want;
set gus;
cpf_number = input(compress(cpf,'.'),32.);
format cpf_number 16.;
run;
Fluorite | Level 6

Re: How to remove dots from numerics

Its work!!

Thank you, so much!!

CVF                                                                New_cvf

Super User

Re: How to remove dots from numerics

Even without the periods you will probably still want to keep that variable as a string.  I doubt that you are going to take the MEAN or do other arithmetic on those values.

Fluorite | Level 6

Re: How to remove dots from numerics

I got you!
I will use to do a Inner join with another table, which I have just numbers without periods Do you think it's gonna work?
Super User

Re: How to remove dots from numerics

If the other table has the values stored as a number then using a number would be easier to match.  With character strings you have to worry about leading zeros (or leading spaces). But that assumes the string of digits are short enough that they resulting integer can be stored exactly into the floating point numbers that SAS uses.

Fluorite | Level 6

Re: How to remove dots from numerics

Actually,

I found this:

`ERROR 23-2: Invalid option name \$20..`

Super User

Re: How to remove dots from numerics

@GUST1 wrote:

Actually,

I found this:

`ERROR 23-2: Invalid option name \$20..`

Show more of the log to see what you did.  You must have done something really creative to get SAS to think you had put the \$20. into a place where it was expecting options.

Fluorite | Level 6

Re: How to remove dots from numerics

Errors (1)
ERROR 23-2: Invalid option name \$20..
Warnings (2)
WARNING: The data set WORK.CPF may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.CPF was not replaced because this step was stopped.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 data cpf;
74 infile cpf \$20.;
____
23
ERROR 23-2: Invalid option name \$20..
75 cards;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CPF may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.CPF was not replaced because this step was stopped

Super User

Re: How to remove dots from numerics

INPUT not INFILE

Fluorite | Level 6

Re: How to remove dots from numerics

tkssss my dear
Discussion stats
• 14 replies
• 8715 views
• 3 likes
• 4 in conversation