BookmarkSubscribeRSS Feed
BBalmaceda
Calcite | Level 5

I am trying to format some following phone numbers: 

 

202.55.1234

4.213.5823

222.345.8893

45.2.785

696.154.55.

 

leading zeroes were dropped  (e.g., “014” became 14 and “004” became 4.  So a phone number of 004-017-0123 is shown as 4.17.123

What would be my best path to get it to a typical xxx-xxx-xxxx format?

6 REPLIES 6
CurtisMackWSIPP
Lapis Lazuli | Level 10

Ugly but it works:

 

data test;
  infile datalines  delimiter=','; 
  input phonenum $12.;
  phonefix = catx('.',put(input(scan(phonenum,1,'.'),3.),z3.),put(input(scan(phonenum,2,'.'),3.),z3.),put(input(scan(phonenum,3,'.'),4.),z4.)));
datalines;
202.55.1234
4.213.5823
222.345.8893
45.2.785
696.154.55.
;
run;
ballardw
Super User

Question: you say "leading zeroes were dropped  (e.g., “014” became 14 and “004” became 4. ". How? When? Were the values like that when you read them into SAS or the result of some operation?

BBalmaceda
Calcite | Level 5
The data was given to me like that... And since it's an assignment my professor doesn't wants me to edit it through SAS
ballardw
Super User

@BBalmaceda wrote:
The data was given to me like that... And since it's an assignment my professor doesn't wants me to edit it through SAS

That is exactly what your question is asking: edit the values with SAS.

 

Rules as to which bits of the string get padded can depend on some of the answers to these questions:

Do you have any values that are completely missing one of the phone number parts such as 123.456 ? (If you imply a 000 area code then the number is definitely invalid, if the the last 4 are missing how do you know that 123 is an area code?)

Or do you have any with extensions like 12.45.234.444? (444 as the extension)

International codes? Which might look like 12.45.234.44 (12 is now an international code and 45 is the area code).

If you have both of these last two cases in your data lots of luck processing correctly.

 

I dealt with a company that did telephone surveys and had some processes to "clean up" phone numbers. It turned out the process would result in some phone numbers from a Latin American country being treated as Colorado USA area codes.

 

 

 

 

Cynthia_sas
SAS Super FREQ

Hi:

  For those who are new to SAS and who might have some questions about the functions, this version of the posted solution shows the functions in smaller steps and produces the final phone numbers either with '.' as the separator or with '-' as the separator in either the original phonenum variable or the new fixed variable.

  This version of the phone will ONLY work if there are separators of . or - between the digits and IF the missing 0 is ALWAYS a leading zero. This code will NOT work if there are + signs in the data, if there are ( ) in the data or if the digits in the final phone number need to be arranged in any order other than 111-222-3333. Here's the "fixed" output:

Cynthia_sas_0-1600474439117.png

 

And, here's the annotated code:

Cynthia_sas_1-1600475998956.png

I color coded the nested functions so you could see which commas and parentheses belonged to each function call. Rather than put ALL the function calls into one gynormous assignment statement, I split them up to make it easier to trace in the output from PROC PRINT. I did modify the original datalines a bit to show either . or - in the input phonenum variable.

 

Cynthia

 

 

Kurt_Bremser
Super User

When were those leading zeroes dropped? Did they exist in the first version (plain text file, delimited file, Excel) of the data you got?

If yes, you just need to fix your import process so it doesn't happen.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1025 views
  • 2 likes
  • 5 in conversation