Changing phone number to a 7 digit number

[BrockJarvie]

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

[SASKiwi]

COMPRESS removes blanks. Here is one way:

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

[BrockJarvie]

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

[SASKiwi]

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

[BrockJarvie]

So are you suggesting I make a new computed column? 

How would you attempt it?

[SASKiwi · Accepted solution]

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

[TomKari]

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