Hi,
I have data sets that have mixed formats of phone numbers such as:
+1 (915) 111-1111
+ (202) 509-3114
+1 (915) 1234567
+ (202) 5093114
(777) 123-4567
3334445678
123.222.1334
How can I standardize the numbers into the following format:
717-333-4545
Thank you
Another task for one of my favourite functions: prxchange:
data have;
input phoneNumber $20.;
datalines;
+1 (915) 111-1111
+ (202) 509-3114
+1 (915) 1234567
+ (202) 5093114
(777) 123-4567
3334445678
123.222.1334
;
data want;
set have;
phoneNumber = prxchange('s/.*(\d{3})\D*(\d{3})\D*(\d{4})/$1-$2-$3/', -1, phoneNumber);
run;
.* matches anything
(\d{3}) = three digits, the brackets create a capture-group
\D* = zero or more non-digits
$1 = the first capture-group
Another task for one of my favourite functions: prxchange:
data have;
input phoneNumber $20.;
datalines;
+1 (915) 111-1111
+ (202) 509-3114
+1 (915) 1234567
+ (202) 5093114
(777) 123-4567
3334445678
123.222.1334
;
data want;
set have;
phoneNumber = prxchange('s/.*(\d{3})\D*(\d{3})\D*(\d{4})/$1-$2-$3/', -1, phoneNumber);
run;
.* matches anything
(\d{3}) = three digits, the brackets create a capture-group
\D* = zero or more non-digits
$1 = the first capture-group
This actually worked thank you so much
Do you have any international numbers? These may show up as 2 digits in front of other digits and not in a typical 3 digit area code , 3 digit exchange and 4 digit block.
Learned this the hard way with code similar to @andreas_lds used on some international numbers in the middle of data that was mostly US that moved them from Central America to Colorado because the local phone number was 2 digits shorter than than the US and the international code provided just enough to replace them and make something that looked like a US phone number.
Make sure you deal correctly with
+43699555123445
Simply discarding the global country code will get you in trouble.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.