topic Re: Removing incorrect phone numbers from 1 million records in ODS and Base Reporting

Removing incorrect phone numbers from 1 million records

Shivi82 — Tue, 01 Dec 2015 08:59:09 GMT

Dear Team,

I have working on a data which has 1 million observations.

This data contains the phone numbers of our customers.

During the booking process the resources enter the phone numbers of our clients however there are many instances where an incorrect phone number has been entered lets says "9999999999". <this is an india format with 10 digits and no country code>.

I have run proc freq on this data and can see there is approx 19.5% of the data which is incorrect i.e. in the format like "9999999999", "1111111111" etc which are written in varuous ways.

I need to delete this data and only keep the valid phone numbers.

running if delete statement would have done the trick however as i mentioned the ph number is written in various ways (all numeric).

Kindly suggest an easy way if possible. I am working on SAS developer.

Regards, Shivi

Re: Removing incorrect phone numbers from 1 million records

FreelanceReinh — Tue, 01 Dec 2015 09:12:22 GMT

Hi Shivi,

There is a parallel thread about almost the same topic: https://communities.sas.com/t5/Base-SAS-Programming/Processing-fields-based-on-value-s/m-p/237105#M43454. Part of the recommendations given there may be applicable to your case.

Re: Removing incorrect phone numbers from 1 million records

RW9 — Tue, 01 Dec 2015 09:20:11 GMT

Ok, the easy way:

Write down what a valid phone number consists of, for example:

- Comprises of 9 numbers

- All numeric

- Prefixed by 001

Write your datastep logic to match this:

data want;
  set have;
  select;
    case (length(phone)>9) delete;
    case (compress(phone," ","d")) delete;
    case (substr(phone,1,3) ne "001") delete;
    otherwise;
  end;
run;

Re: Removing incorrect phone numbers from 1 million records

Shivi82 — Tue, 01 Dec 2015 11:39:43 GMT

Thanks for sharing the link but this has made me go bonkers. The article shared in the link was enormous and very difficult to understand as not really an expert on sas macros.