- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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,
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This is a character variable, correct?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A numeric variable with the BEST format applied would not show up like that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
infile cpf $20. ;
cards;
838.399.200.13
;
data want;
set gus;
cpf_number = input(compress(cpf,'.'),32.);
format cpf_number 16.;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Its work!!
Thank you, so much!!
CVF New_cvf
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Actually,
I found this:
ERROR 23-2: Invalid option name $20..
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
INPUT not INFILE
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content