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

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

Louis44 — Fri, 01 Mar 2013 22:26:38 GMT

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!

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

Reeza — Fri, 01 Mar 2013 22:53:18 GMT

VTYPE function

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

Louis44 — Fri, 01 Mar 2013 23:44:33 GMT

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

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

Reeza — Sat, 02 Mar 2013 00:09:48 GMT

Step 1 - Convert all to characters

Step 2 - Compress out anything that's not numbers

Step 3 - Format as character.

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

Louis44 — Sat, 02 Mar 2013 00:14:39 GMT

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.

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

Reeza — Sat, 02 Mar 2013 02:23:33 GMT

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

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

Tom — Sat, 02 Mar 2013 04:56:28 GMT

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

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

Louis44 — Mon, 04 Mar 2013 17:52:03 GMT

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