topic Re: SAS EG to Clean Email Addresses in SAS Enterprise Guide

SAS EG to Clean Email Addresses

tessa_h — Fri, 14 Oct 2016 14:56:44 GMT

Hi All,

Forgive me as I am a new SAS EG user (version 7.1); I have been assigned a data scrubbing task and it's been requested that I use SAS EG in order to do so.

I've been moving forward fairly well; however, I've encountered a problem that I cannot seem to intuitively resolve. I have several data sets which all have email addresses. Some of my email addresses contain junk characters which I have used the compress function to remove.

Unfortunately, I also have email addresses where I'd like to remove some characters based on where in the email address they are positioned.

Here are 2 examples:

Email Address: ..johndoe@gmail.com

Email Address: johndoe@gmail.com..

Obviously you can see what I need to do.

I'd like to use an advanced expression to

1. Remove all non-alphabetic characters before the first alphabetic character in the email string

2. Remove all characters after ".com"

Thoughts?

Thanks in advance,

Tessa

Re: SAS EG to Clean Email Addresses

ballardw — Fri, 14 Oct 2016 15:22:30 GMT

Before blindly going after "everything after .com" you may want to verify that you have nothing like

bill@this.companyname.com in your data.

Re: SAS EG to Clean Email Addresses

tessa_h — Fri, 14 Oct 2016 15:25:12 GMT

Fair statement- without combing through, manually, hundreds of thousands of rows of data, do you have a solution that can help me out?

I also realized that I will have other situations like .gov, .ca, .edu....so perhaps the question is more to remove non alphabetic characters at the end of the email string, regardless of email info.

Make sense?

Re: SAS EG to Clean Email Addresses

cjinsf — Fri, 14 Oct 2016 16:33:18 GMT

using regular expressions probably better then below... what about numbers at beginning of email address like "123test@yahoo.com"? If numbers need to be kept replace 'anyalpha' with 'anyalnum'.

data email_1;
length email $40;
infile cards;
input email $;
cards;
..johndoe@gmail.com
johndoe@gmail.com..
;
run;

proc sql;
create table email_1a as select
t1.email,
reverse(substr(reverse(trim(substr(t1.email,anyalpha(t1.email),length(t1.email) - anyalpha(t1.email)+1))),anyalpha(reverse(trim(substr(t1.email,anyalpha(t1.email),length(t1.email) - anyalpha(t1.email)+1)))),length(reverse(trim(substr(t1.email,anyalpha(t1.email),length(t1.email) - anyalpha(t1.email)+1)))) - anyalpha(reverse(trim(substr(t1.email,anyalpha(t1.email),length(t1.email) - anyalpha(t1.email)+1))))+1)) as email_corr
from email_1 t1
;
quit;

Re: SAS EG to Clean Email Addresses

BrunoMueller — Fri, 14 Oct 2016 16:32:35 GMT

As a start you could use the compress function, like below, this will remove all characters, except alphabetic, digits and punctuation anywhere in your email. Maybe one should also check what makes up a valid email address, see here https://en.wikipedia.org/wiki/Email_address

Maybe there are Regular expressions that check for valid email addresses as well. You could use those with the PRX... functions.

Also have a look at this discussion

https://communities.sas.com/t5/SAS-Procedures/validate-email-address/m-p/37459#U37459

data have;
  length email newEmail $ 256;
  email = cats("090a0d"x, "sugus-sugus_sugus.1234@sugus.com.edu", "01"x);

  newEmail = compress(email, "" , "adpk");
  putlog _all_;
run;

Bruno

Re: SAS EG to Clean Email Addresses

tessa_h — Fri, 14 Oct 2016 18:22:55 GMT

Thanks, Bruno, for your advice...

Couple of things- I'm already using the compress function to remove some characters. This has been successful so far...

Second, the link to the discussion has an article in it- the link goes to an error page. Not too useful 😞

A colleague of mine pointed me to this article, however, as a new to SAS user, it will take some time to fully understand and implement how this ought to work for the project I am working on.

To be perfectly honest as well, I'm also new to SQL and sadly, I've been tossed into the fire. Data scrubbing isn't a normal job function of mine and it sort of landed on my plate. However, I'm always keen to add more skill sets...so please forgive my lack of knowledge as I get through this...I am well aware that I'm in over my head.

Re: SAS EG to Clean Email Addresses

tessa_h — Fri, 14 Oct 2016 18:27:09 GMT

I appreciate the perspective; it's always helpful to get some insight to a problem. However, I don't appreciate an implication that I'm "blindly" going into a dataset without a variety of considerations.

Thanks

Re: SAS EG to Clean Email Addresses

ChrisHemedinger — Fri, 14 Oct 2016 19:01:38 GMT

I don't think @ballardw meant anything by that, other than to be helpful. As someone who also works with e-mail addresses, I'm always surprised by the number of variations caused by "subdomains" in a single organization. E-mail addresses have an @ symbol and end with a "dot something", but beyond that they seem to defy your typical expected formulas.

Re: SAS EG to Clean Email Addresses

tessa_h — Fri, 14 Oct 2016 19:11:26 GMT

That's one of the downfalls of communicating via a forum like this- no tone, no facial expression, etc...regardless of the intention of the communication, it's critically important to consider how a message can be received. Not to mention that when I have used forums like this for help in the past, there seems to be a population of elitist types who enjoy pointing out the errors that others have made...just to enhance a grandiose sense of self. To avoid that type of issue, I included my level of knowledge using the program; I am the first to admit that I don't know what I don't know...I anticipate that moving forward with additional questions as they come up, I am sure to encounter these personality types. But to your point, it's pretty amazing the amount of considerations...overwhelming, in fact...and as I slowly remove layer after layer to this half a million row problem, I discover a new consideration...ie .com is one thing...but then...com, .edu, .gov, etc etc etc....there is no single easy solution. I appreciate help in this frustrating process that is not unique to me. 🙂

Re: SAS EG to Clean Email Addresses

ChrisHemedinger — Fri, 14 Oct 2016 19:21:32 GMT

@tessa_h wrote:
... when I have used forums like this for help in the past, there seems to be a population of elitist types who enjoy pointing out the errors that others have made...just to enhance a grandiose sense of self.

Welcome to the SAS Support Communities -- most of the experts here answer with humility, and a genuine desire to help. Once in a while there is a language/culture barrier (it's a global community), but if you assume that people have the best intentions, you shouldn't be disappointed.

Regarding your challenge, this paper might help.

Re: SAS EG to Clean Email Addresses

TomKari — Fri, 14 Oct 2016 21:08:32 GMT

You've definitely been tossed in. I just looked up the rules for valid versus invalid email address strings, and it's pretty complicated.

Here's a reference to the rules:

https://en.wikipedia.org/wiki/Email_address

I'll see what I can do over the next few days...right now, I'm under a tight deadline. Regular expressions are the way to go...note that if you can find a regular expression example in Perl, it should translate well to SAS.

Tom

Re: SAS EG to Clean Email Addresses

ballardw — Mon, 17 Oct 2016 18:53:34 GMT

I did not mean anything negative about the "blind" comment. We have new posters here with very wide differences in experience. It is not uncommon to see a "desired result" phrasing that may not do what is actually intended as specified. Often the statement is incomplete or misses a boundary condition such that the suggestions made by community members does exactly what was requested it is not what the poster actually intended.

Often a reminder to look at all of the data helps clear up rules specifications.

I do sympathize as I have spent lots of time scrubbing data in a fairly large range of topics. One of my favorite examples is before a survey we provided the data recorders instructions about how to enter expected names. One example International Buisness Machines or IBM in any form was to be entered as "IBM". At this point take a short break and think about how many ways that may have been actually entered into the data.

If your guess was over 10 you may be getting paranoid enough to be a good data scrubber. The actual count was 18 different forms with the most entertaining being I>B>M>. Which one will note immediately is the result of holding the shift key down and typing a period after each letter. Note that the original instructions specifically did NOT include a period. Other spellings involved random shifts of capitalization combined with one or more periods after letters (not always all 3 letters).

So when I saw a request similar to "trim everything after .com" I thought it may be appropriate to remind other valid values may exist. And your response about .gov and such showed that you picked up on the issue quickly.

Re: SAS EG to Clean Email Addresses

Patrick — Sat, 15 Oct 2016 01:45:50 GMT

Cleansing email addresses is nasty business and you're starting here with a rather difficult task.

You don't have to use a lot of "SAS" though to get this job done. Perl Regular Expressions (RegEx) are great for pattern matchin and pattern replacement.

But first just to answer your initial question:

data have;
  input email_address :$40.;
  email_address_want=email_address;

  /* Remove all non-alphabetic characters before the first alphabetic character in the email string */
  email_address_want=prxchange('s/^\s*[[:^alpha:]]+//oi',1,email_address_want);

  /*  Remove all characters after ".com" */
  email_address_want=prxchange('s/(?<=(\.com)).+$//oi',1,email_address_want);

  /* Alternative: Remove all non-alphanumeric characters at end of the email address */
/*  email_address_want=prxchange('s/[[:^alnum:]]+\s*$//oi',1,email_address_want);*/
datalines;
..johndoe@gmail.com
johndoe@gmail.com..
johndoe@gmail.com.au&%$.
*&^%$5467johndoe@gmail.org&%$.
;
run;

SAS implemented RegEx and it's available via SAS functions and call routines starting with PRX...

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0w6napahk6x0an0z2dzozh2ouzm.htm

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003288497.htm

https://support.sas.com/rnd/base/datastep/perl_regexp/regexp-tip-sheet.pdf

RegEx are widely used and email verification is a common application for RegEx. You will find heaps of examples on the Internet.

What I would do is to first implement a RegEx for email verification - so a RegEx which defines the pattern for a valid email address (search the Internet for such a RegEx as starting point).

Then create a "bad' file with all email addresses not conforming to this pattern and start developing and testing your RegEx for cleansing.

As others already pointed out the actual RFC standard for valid email addresses is astonishingly wide. Depending on where your data comes from (like anything from the Internet or just a set of company email addresses) you will want to narrow down the pattern for valid email addresses to suit your data.

If you don't know RegEx syntax: It's a bit tidious but very worth learning.

Good luck!

Re: SAS EG to Clean Email Addresses

TomKari — Sat, 15 Oct 2016 13:29:49 GMT

Just to get you started, I pulled a simple Regex example off of the internet, from

http://www.regular-expressions.info/email.html

Here's a little piece of code that will use the example regex to divide the addresses into good or bad.

BTW, I can confirm what every has said about @ballardw. What was said was only in the spirit of being helpful; any other interpretation is due to the nature of the conversation.

Tom

data Good Bad;

if _n_ = 1

then do;

retain PRX1;

PRX1 = prxparse("/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\s*$/i");

drop PRX1;

end;

set Have;

if prxmatch(PRX1, TestString) then

output Good;

else output Bad;

run;

Re: SAS EG to Clean Email Addresses

tessa_h — Mon, 17 Oct 2016 11:42:03 GMT

Ohhh yes, I'm very aware of the billion different ways a person can enter in info...back in the day when all I DID was scrub data...how many times to you think the word "feline" can be messed up?

feline

feelin

feeline

feilin

filin

feillin

...

Yeah...that was always fun... 😐

Re: SAS EG to Clean Email Addresses

tessa_h — Mon, 17 Oct 2016 13:11:29 GMT

Hi All:

First of all- THANK YOU for speaking up! All of this info is helpful in pointing me in the right direction. It also helps to know that what I am trying to accomplish is a big job. I can't tell you how much it helps to be validated around my concerns.

I can see that there are many ways to skin this cat (sorry for the grotesque pun...but it's a grotesque job!)...and figuring out the appropriate method is going to be a task in it of itself.

I've reached out to some collegues for assistance- turns out that we have a process in place for validating emails; I will be meeting with the gentleman who has established this process. I hope to share the information you all have provided me to see how his methodology compares. I'd like to use coding from you-all and him...and build it right into my workflow. I will let you know what his response and method is.

Thanks again- and my apologies if I have previously offended anyone; like I had mentioned before, sometimes finding help on forums can be challenging due to communication barriers and egos. 😛

-Tessa

Re: SAS EG to Clean Email Addresses

SASKiwi — Wed, 19 Oct 2016 19:30:11 GMT

Thought it is worth mentioning that SAS's Dataflux product contains functionality for cleaning email addresses along with cleaning all sorts of other data besides. This interesting paper explains more:

http://support.sas.com/resources/papers/proceedings15/SAS1852-2015.pdf