topic Re: How to remove dots from numerics in New SAS User

How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 18:22:22 GMT

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,

Re: How to remove dots from numerics

PeterClemmensen — Mon, 25 Jan 2021 18:30:19 GMT

This is a character variable, correct?

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 18:34:27 GMT

Re: How to remove dots from numerics

Reeza — Mon, 25 Jan 2021 18:48:13 GMT

Are you using an English language or a different language?

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

Re: How to remove dots from numerics

Tom — Mon, 25 Jan 2021 19:24:49 GMT

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;

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 21:12:46 GMT

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;

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 21:14:06 GMT

Its work!!

Thank you, so much!!

CVF New_cvf

Re: How to remove dots from numerics

Tom — Mon, 25 Jan 2021 21:23:29 GMT

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.

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 21:27:14 GMT

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?

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 21:29:10 GMT

Actually,

I found this:

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

Re: How to remove dots from numerics

Tom — Mon, 25 Jan 2021 21:45:07 GMT

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.

Re: How to remove dots from numerics

Tom — Mon, 25 Jan 2021 21:46:23 GMT

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

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 21:56:31 GMT

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;

73 data cpf;

74 infile cpf $20.;

____

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

Re: How to remove dots from numerics

Tom — Mon, 25 Jan 2021 22:00:49 GMT

INPUT not INFILE

Re: How to remove dots from numerics

GUST1 — Mon, 25 Jan 2021 22:11:13 GMT

tkssss my dear