topic Re: How to convert to a Standard Phone Number? in SAS Programming

How to convert to a Standard Phone Number?

LMSSAS — Mon, 18 Jul 2022 14:21:50 GMT

I have the following list of numeric phone numbers and I want to convert them to standard phone number. Can someone recommend the best code for this? I cannot figure out where to even start with this.

Re: How to convert to a Standard Phone Number?

ballardw — Mon, 18 Jul 2022 14:36:42 GMT

The A at the head of the column says the values are character, which is likely best for phone numbers as you shouldn't do arithmetic with them, not numeric.

Since I am familiar with about a dozen "standard" formats for telephone numbers you need to show what you expect for a result. If it involves adding spaces, hyphens, parentheses, dots or other character then likely you need to create a new variable to hold the additional characters.

One way:

data example;
   phone='9046557747';
   newphone=cat('(',substr(phone,1,3),') ',substr(phone,4,3),'-',substr(phone,7,4));
run;

Likely whatever you use will involve the 3 calls to the substr function if you want area code, exchange and number.

However, if any of you numbers involving international numbers with a national code you will need to determine that and apply different rules.

Re: How to convert to a Standard Phone Number?

LMSSAS — Mon, 18 Jul 2022 14:45:41 GMT

@ballardw I missed that these are characters, thanks for pointing that out.

I do not have any international all US numbers. The format I'm looking for (904) 655-7747. it looks like the code you provided will give me that format.

One more question, I have an entire list of phone numbers, how do I get SAS to consume the entire column and not just a specific phone number? this is an elementary question, I know. Thank you!

Re: How to convert to a Standard Phone Number?

ballardw — Mon, 18 Jul 2022 15:03:33 GMT

@LMSSAS wrote:

@ballardw I missed that these are characters, thanks for pointing that out.

I do not have any international all US numbers. The format I'm looking for (904) 655-7747. it looks like the code you provided will give me that format.

One more question, I have an entire list of phone numbers, how do I get SAS to consume the entire column and not just a specific phone number? this is an elementary question, I know. Thank you!

I am not sure what you mean by "consume the entire column".

If this has anything to do with reading in an external file then you should provide an example of the file and the desired results.

Re: How to convert to a Standard Phone Number?

Tom — Mon, 18 Jul 2022 15:18:53 GMT

@LMSSAS wrote:

...

One more question, I have an entire list of phone numbers, how do I get SAS to consume the entire column and not just a specific phone number? this is an elementary question, I know. Thank you!

You would have to work very hard to have it NOT do the same thing for every observation.

That is how a basic data step works.

data want;
  set have;
....
run;

Re: How to convert to a Standard Phone Number?

LMSSAS — Mon, 18 Jul 2022 15:24:11 GMT

@ballardw I have an entire column in my sas sata set of diffrent phone numbers. if i use the code below i get blanks or Nulls, since I can not enter each phone number individually, I was asking if there is a way to transform every phone number in my sas data set to a standard phone number.

data example;
phone=AGENT_WORK_PHONE_NUMBER;
newphone=cat('(',substr(phone,1,3),') ',substr(phone,4,3),'-',substr(phone,7,4));
run;

Re: How to convert to a Standard Phone Number?

LMSSAS — Mon, 18 Jul 2022 15:49:16 GMT

Thanks, Tom
Yes, i guess that was my last question. Is it a Dats Step that would create this?
It sounds like all the (original) phone characters would have to be identical for this to wrk.

Re: How to convert to a Standard Phone Number?

ballardw — Mon, 18 Jul 2022 16:11:41 GMT

@LMSSAS wrote:

Thanks, Tom
Yes, i guess that was my last question. Is it a Dats Step that would create this?
It sounds like all the (original) phone characters would have to be identical for this to wrk.

The PHONE variable I created as an example. That is not your variable.

Replace "Phone" with the name of your variable that I thought was just too long to type for an example since you only provided a PICTURE of data. Do not use the "phone= ...;" line as that was just to provide one value as an example of how the code might work.

Re: How to convert to a Standard Phone Number?

Tom — Mon, 18 Jul 2022 16:12:50 GMT

@LMSSAS wrote:

Thanks, Tom
Yes, i guess that was my last question. Is it a Dats Step that would create this?
It sounds like all the (original) phone characters would have to be identical for this to wrk.

I think you are just confusing yourself. Since you did not post any example data (just a photograph) someone replied with an example using a single example phone number to show how it could work. Don't use the the example phone number. Just use the variable you already have with the phone numbers in it. The key thing you need to take is the code that converts the strings.

So IF your actual dataset is named HAVE a looks something like this data.

data have;
  input phone $10.;
cards;
9046557747
2015551234
;

Then the data step you want to run to create the new dataset named WANT will look something like this:

data want;
  set have;
  length pretty_phone $13;
  pretty_phone=cats('(',substr(phone,1,3),')',substr(phone,4,3),'-',substr(phone,7));
run;

So each iteration of the data step will take the current value of the variable (a.k.a. "column") PHONE and generate the new variable PRETTY_PHONE.

Results:

Obs      phone       pretty_phone

 1     9046557747    (904)655-7747
 2     2015551234    (201)555-1234

To run it for your data just change the input and output dataset names and the input and output variable names.

Re: How to convert to a Standard Phone Number?

ghosh — Mon, 18 Jul 2022 17:07:35 GMT

I'd suggest use the tools SAS provides, a picture format in this case. You can modify the proc format picture statement to test for incorrrect telephone numbers.
Edit: Just noticed your input data is aphanumeric, so I modified my original code

proc format;
  picture telno (default=16)
    low - high= '999) 999-9999' (prefix='(');
 run;
 
 data have;
 length agt_wrk_num $16;
 input agt_wrk_num $;
 agt_wrk_conv=put(input(agt_wrk_num,best.),telno.);
 datalines;
 9046557747
 9547538080
 2399616606
 ;
  
 proc print;
 run;

Re: How to convert to a Standard Phone Number?

pink_poodle — Mon, 18 Jul 2022 17:57:46 GMT

You would need to convert variable phone to numeric type first:
phone_num = input(phone, 8.);

Re: How to convert to a Standard Phone Number?

LMSSAS — Tue, 19 Jul 2022 18:49:13 GMT

Thank you!

Re: How to convert to a Standard Phone Number?

LMSSAS — Tue, 19 Jul 2022 18:49:29 GMT

Thank you!