The SAS Output Delivery System and reporting techniques

Removing incorrect phone numbers from 1 million records

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Removing incorrect phone numbers from 1 million records

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


Accepted Solutions
Solution
‎12-01-2015 06:38 AM
Super User
Super User
Posts: 7,668

Re: Removing incorrect phone numbers from 1 million records

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,115

Re: Removing incorrect phone numbers from 1 million records

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#M4.... Part of the recommendations given there may be applicable to your case.

Frequent Contributor
Posts: 92

Re: Removing incorrect phone numbers from 1 million records

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.
Solution
‎12-01-2015 06:38 AM
Super User
Super User
Posts: 7,668

Re: Removing incorrect phone numbers from 1 million records

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;
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 440 views
  • 1 like
  • 3 in conversation