topic Re: Extract Information from text in SAS Programming

Extract Information from text

Selli5 — Thu, 30 Mar 2017 04:04:35 GMT

Hello,

I need to extract the phone numbers out of a text field to establish a new cleaned contact mobile number variable and contact landline variable. There is no set order the phone numbers are written in the text field. Attached is a sample file. Can anyone help?

thanks

Sally

Re: Extract Information from text

Madansas7b — Thu, 30 Mar 2017 05:07:39 GMT

as of my understand to keep only numbers there are several ways .and you can use like bellow

num = compress(long_string, , 'kd');

Re: Extract Information from text

Patrick — Thu, 30 Mar 2017 08:33:23 GMT

The way I would approach this task:

1. Data exploration and profiling

Scan through your data and collect as many different patterns of phone numbers as you can, i.e. string of 10 digits, first 2 digits 04

2. Define the business rules

Define the extraction rules for your phone numbers, i.e. string with 10 digits, first 2 digits 04 -> mobile number

3. Define sequence how to apply business rules

Example: if substring of digits points to landline number but there is also the word "Mobile" in the string: What comes first? Will this be qualified as a landline or a mobile number? Eventually plan also for field which contains data quality score which in such a case wouldn't be 1

4. Implement

Now that you've got the business rules and sequence of rules you can implement via a data step and a set of IF.. THEN.. ELSE statements.

5. Test

A: Run your code against the sample data containing all the cases. Verify if result is as expected/defined in the business rules.

B: Run your code agains the full data set. Check log for any signs of issues, check that for all source string a phone number could get extracted (or that there is a clear explanation why this didn't happen).

Once you get stuck in step 4. on how to technically implement a business rule then come back to this forum, provide a data step which creates sample data with the source string, provide the business rule, the not yet working code you've already developed and show us how the expected result should look like when applied on the sample data you've provided.

Re: Extract Information from text

lakshmi_74 — Thu, 30 Mar 2017 09:55:48 GMT

From your dataset i am seeing the same number with the text of specific category('land','mobile) number in the column of current_phone. Can you let us know what extactly you are looking for the output?

Re: Extract Information from text

Selli5 — Thu, 30 Mar 2017 23:24:57 GMT

Hi,

I have a current phone variable which has mixed text with the phone numbers. I would like to create a new phone variable that just had the phone numbers.

Secondly I would like all mobile numbers beginning with (4 or 04) to All begin with 04.
And numbers beginning with 08 or 8 or 9 with a state = WA to All begin 08. and so on for all states.

CURRENT PHONE   STATE
0418448841MOBILE   WA
FAX90227506   WA
CAR0L'S MOBILE 0409211082   WA
890213600   WA
90214178   WA
427866574   WA
(HOME90930658)   NSW
418934400   WA
0439394323/DESMOND   WA

thanks
Sally