topic Re: Changing phone number to a 7 digit number in SAS Enterprise Guide

Changing phone number to a 7 digit number

BrockJarvie — Tue, 13 Oct 2020 00:39:33 GMT

Hi, I'm working on a problem that wants me to create an advanced filter for a 'Phone_number" variable, that removes the extension at the beginning of the phone numbers, making them 7 digit numbers. The extensions are +61(2)', '+61(3)', and '+61(5)'

Here is what I've been trying : COMPRESS(t1.Phone_Number, "+61(2)", "+61(3)", "+61(5)")

Could someone please tell me what I'm doing wrong and provide the correct FUNCTION to use in this scenario.

Thanks

Re: Changing phone number to a 7 digit number

SASKiwi — Mon, 12 Oct 2020 23:09:04 GMT

COMPRESS removes blanks. Here is one way:

data _null_;
  phone = '+61(2)1234567';
  phone = transtrn(phone, '+61(2)', '');
  put _all_;
run;

Re: Changing phone number to a 7 digit number

BrockJarvie — Tue, 13 Oct 2020 00:20:29 GMT

And this will work as an advanced filter to a pre-existing column in the Querybuilder?

Re: Changing phone number to a 7 digit number

SASKiwi — Tue, 13 Oct 2020 00:30:14 GMT

A filter selects rows it doesn't transform values so no.

Re: Changing phone number to a 7 digit number

BrockJarvie — Tue, 13 Oct 2020 00:59:19 GMT

So are you suggesting I make a new computed column?

How would you attempt it?

Re: Changing phone number to a 7 digit number

SASKiwi — Tue, 13 Oct 2020 02:04:37 GMT

You could try pasting the relevant statement into the QueryBuilder as a new computed column - I guess it is SQL:

transtrn(phone, '+61(2)', '') as phone_new

Re: Changing phone number to a 7 digit number

TomKari — Tue, 13 Oct 2020 15:36:29 GMT

You should be able to do it in a generalized way using regular expressions. Using this code to create some data:

data Have;
	length Phone_Number $20;
	input Phone_Number;
	cards;
+61(2)1234567
+61(3)9876543
+61(5)2223333
run;

create a new query on the data. Set up a new Advanced Expression, and in the "Enter an expression:" box, copy and paste:

prxchange("s/(\+[[:digit:]]+\([[:digit:]]\))([[:digit:]]+)/$2/", -1, Phone_Number)

Tom