BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
LMSSAS
Quartz | Level 8

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.  

 

LMSSAS_0-1658153935766.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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.

 

 

View solution in original post

12 REPLIES 12
ballardw
Super User

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.

LMSSAS
Quartz | Level 8

@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!

ballardw
Super User

@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.

LMSSAS
Quartz | Level 8

@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.  

 

LMSSAS_1-1658157673804.png

 

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

LMSSAS_0-1658157609387.png

 

pink_poodle
Barite | Level 11

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

Tom
Super User Tom
Super User

@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;
LMSSAS
Quartz | Level 8

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.
ballardw
Super User

@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.

 

 

LMSSAS
Quartz | Level 8
Thank you!
Tom
Super User Tom
Super User

@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.

 

LMSSAS
Quartz | Level 8
Thank you!
ghosh
Barite | Level 11

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;
 

 

ghosh_0-1658162018011.png

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 3595 views
  • 2 likes
  • 5 in conversation