topic Re: Extracting numbers from Excel cell with numbers and text in New SAS User

Extracting numbers from Excel cell with numbers and text

iressa131 — Thu, 31 Jan 2019 04:45:21 GMT

Hello!

I'm trying to extract phone numbers from a data set where they are included in cells with text data (such as emails and name, etc). How would I go about doing this? Another problem is that the entries are not all formatted the same. For example the phone numbers are written in different ways as follows.

123.456.7890

123-456-7890

(123) 456-7890

There are 3500 entries so there may be variations I haven't seen.

Thank you for any help you can provide!

Re: Extracting numbers from Excel cell with numbers and text

Reeza — Thu, 31 Jan 2019 05:21:58 GMT

@iressa131 wrote:

Hello!

I'm trying to extract phone numbers from a data set where they are included in cells with text data (such as emails and name, etc). How would I go about doing this? Another problem is that the entries are not all formatted the same. For example the phone numbers are written in different ways as follows.

123.456.7890

123-456-7890

(123) 456-7890

There are 3500 entries so there may be variations I haven't seen.

Thank you for any help you can provide!

Use the COMPRESS() function.

PhoneNum = compress(oldVarName, , ‘kd’);

Re: Extracting numbers from Excel cell with numbers and text

Ksharp — Thu, 31 Jan 2019 13:23:19 GMT

data have;
input x $40. ;
want=prxchange('s/\D*(\d\d\d)\D+(\d\d\d)\D+(\d\d\d\d)\D*/$1 $2 $3/o',-1,x);
cards;
123.456.7890
123-456-7890
(123) 456-7890
;
run;


proc print;run;

Re: Extracting numbers from Excel cell with numbers and text

ballardw — Thu, 31 Jan 2019 15:54:11 GMT

@iressa131 wrote:

Hello!

I'm trying to extract phone numbers from a data set where they are included in cells with text data (such as emails and name, etc). How would I go about doing this? Another problem is that the entries are not all formatted the same. For example the phone numbers are written in different ways as follows.

123.456.7890

123-456-7890

(123) 456-7890

There are 3500 entries so there may be variations I haven't seen.

Thank you for any help you can provide!

Check the lengths of any of these after the digits only are preserved. You may find extensions or international codes in longer than expected values, missing area codes (7 digit).

Then go back to who ever designed the data entry and use a somewhat more modern approach that has area code, exchange (the second 3 digit group) and number entry that will collect these consistently.

Collecting cleanly is preferred over cleaning collection.

Re: Extracting numbers from Excel cell with numbers and text

iressa131 — Sat, 02 Feb 2019 15:46:33 GMT

Thanks for the reply! When I run the code I get the following

Re: Extracting numbers from Excel cell with numbers and text

iressa131 — Sat, 02 Feb 2019 16:14:09 GMT

I just realized the quotation marks are incorrect because Im using university. so now when I run it I don't get any errors however my output only shows 1 observation with no entry.

PROC CONTENTS DATA=WORK.IMPORT; RUN;
data WORK.IMPORT;
PhoneNum = compress(agent_details, ,'kd'); 
run;

Re: Extracting numbers from Excel cell with numbers and text

Reeza — Sat, 02 Feb 2019 17:46:15 GMT

@iressa131 wrote:

I just realized the quotation marks are incorrect because Im using university. so now when I run it I don't get any errors however my output only shows 1 observation with no entry.
PROC CONTENTS DATA=WORK.IMPORT; RUN;
data WORK.IMPORT;
PhoneNum = compress(agent_details, ,'kd'); 
run;

You didn't include an input data set, in fact you overwrote your data. You'll need to import it again.

data Step1; *output data set name is STEP1;

set IMPORT; *input data set name is IMPORT;

PhoneNum = compress(agent_details, ,'kd'); 

run;