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

[Louis44]

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!

[Reeza]

VTYPE function

[Louis44]

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

[Reeza]

Step 1 - Convert all to characters

Step 2 - Compress out anything that's not numbers

Step 3 - Format as character.

[Louis44]

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.

[Reeza]

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

[Tom · Accepted solution]

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

[Louis44]

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