Removing character strings in a variable using advanced filter in Querybuilder

[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

[jimbarbour]

COMPRESS treats the second parameter as a list of characters to get rid of.  For example, "+61(2)" will get rid of all 6's, 1's, and 2's, no matter where they are.

TRANSTRN is probably the better function for this purpose.  TRANSTRN can translate a string in to a null (i.e. remove without replacing it with anything).  TRANWRD would probably also work but leaves a space in place afterwards which would have to be removed by a LEFT or a STRIP or a similar function.

If they're all of the same length, it would be easy to use SUBSTR as well.

Jim

[BrockJarvie]

Thank you!

[jimbarbour]

You're welcome.

Here are a couple of examples if they're helpful.  The below code produces two "cleaned" phone numbers (no prefix).  One was produced via the TRANSTRN, the other by SUBSTR.  Notice that the SUBSTR is a lot easier to code if all your prefixes are of equal length.  If they're not all of equal length, then you'd probably have to find the closing parenthesis and add 2 in order to get the second parameter for the SUBSTR.   Either one will work as will TRANWRD.

Below the code are the results.

Jim

DATA	Cleaned_Phone_Numbers;
	SET	Raw_Phone_NUmbers;

	Phone_No1	=	TRANSTRN(Phone_No,  '+61(2) ', STRIP(''));
	Phone_No1	=	TRANSTRN(Phone_No1, '+61(3) ', STRIP(''));
	Phone_No1	=	TRANSTRN(Phone_No1, '+61(5) ', STRIP(''));
	Phone_No2	=	SUBSTR(Phone_No, 7);
RUN;

Results, showing original, TRANSTRN results, and SUBSTR results.

[BrockJarvie]

So this should all be done as a computed column right? an advanced filter wouldn't be able to change the current 'Phone_Number' column to the 7-digit number.

[jimbarbour · Accepted solution]

---

@BrockJarvie wrote:

So this should all be done as a computed column right? an advanced filter wouldn't be able to change the current 'Phone_Number' column to the 7-digit number.

---

Yes, I believe that's the right terminology for Query Builder.

It should translate to SQL something along the lines of:

Select SUBSTR(Phone_No, 7) AS Phone_No

If that doesn't work for some reason, please post the log, and we can take a look at it.  When you post the log, please use the Insert Code button on the reply window.

Jim

[BrockJarvie]

Thank you for your help! I was able to create the revised Phone_Number column with the SUBSTRN function.

[jimbarbour]

Perfect! Good job.  👍

Jim