Help using Base SAS procedures

How to work with both Char and Numeric Data Types when manipulating phone numbers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How to work with both Char and Numeric Data Types when manipulating phone numbers

I import many different data files into SAS.  Some of the phone numbers have dashes, some do not.  How can I remove the dashes from the phone numbers for all different data types?  I get an error when I use COMPRESS on numeric data types.

This is what I would like to do:

proc sql;
create table test as
select

COMPRESS(phone_number,'-') as PhoneNumber

from MyTable
;quit;

But this won't work when there isn't already a dash, because it's numeric.  I tried converting it to a char first, but if the phone number is already a char, I can't do that either.

I haven't found a good way to check if a field is numeric or char value yet, either.  Otherwise something like this would work:

select

case when ISNUMERIC(phone_number) then phone_number else COMPRESS(phone_number,'-') end as PhoneNumber

But obviously ISNUMERIC does not exist.  Any ideas?  Thanks for your help!


Accepted Solutions
Solution
‎03-01-2013 11:56 PM
Super User
Super User
Posts: 6,144

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

Use CATS() function. It will handle both character and numeric arguments without issuing warnings.  You might want to set a length of your new variable, otherwise it will probably default to 200.

COMPRESS(cats(phone_number),'-') as PhoneNumber length=10

View solution in original post


All Replies
Grand Advisor
Posts: 16,908

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

VTYPE function

Occasional Contributor
Posts: 9

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

It turns out the CASE statement doesn't work because COMPRESS requires a char expression (even if it's not executed).  And there are formatting issues.

Any way around this?

Is there any way to get COMPRESS to work for both char and numeric values?  Or is there anyway to convert any data type to a char, even if it is already a char?

To sum up, say I have two files where the phone numbers are different data types:

File 1 phone number:  1234567890     (data type numeric)

File 2 phone number:  123-456-7890   (data type char)

For both, the output needs to be 1234567890

Grand Advisor
Posts: 16,908

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

Step 1 - Convert all to characters

Step 2 - Compress out anything that's not numbers

Step 3 - Format as character.

Occasional Contributor
Posts: 9

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

When converting all to characters (step 1 above), should I use PUT?  Because PUT does not work with a value that's already char datatype.  I tried to convert the field to char in a separate data step, but when I got to the proc sql, it thought it was numeric again.

Grand Advisor
Posts: 16,908

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

I think we, or at least I, need some more info on what your trying to do and what code you're using. 

Solution
‎03-01-2013 11:56 PM
Super User
Super User
Posts: 6,144

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers

Use CATS() function. It will handle both character and numeric arguments without issuing warnings.  You might want to set a length of your new variable, otherwise it will probably default to 200.

COMPRESS(cats(phone_number),'-') as PhoneNumber length=10

Occasional Contributor
Posts: 9

Re: How to work with both Char and Numeric Data Types when manipulating phone numbers


CATS works perfectly!  That's exactly what I needed, it works on both sets of data.  Thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 232 views
  • 0 likes
  • 3 in conversation