SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Expression for handling two kinds of invalid email adresses in DI Studio

Reply
Frequent Contributor
Posts: 90

Expression for handling two kinds of invalid email adresses in DI Studio

I've set up a simplified DI Studio job for a problem. It has a source data set that contains invalid email adresses (SS_EMAIL), and an Extract transformation that I'd like to use to correct them. There's two kinds of invalid email adresses:

1) user@.domain.com, which should become user@domain.com

2) user@ domain.com, which should become user@domain.com

What kind of expression can I use to make these two corrections? They'll be fixed in the source data eventually, but that might take weeks so my job must be able to handle them for the time being.

Thanks for your time.

Super User
Posts: 5,441

Re: Expression for handling two kinds of invalid email adresses in DI Studio

Posted in reply to EinarRoed

The exact syntax you will have to write by yourself.

But it could involve finding the position right after the @-sign, and test if this position is not a special char (see NOTALNUM Function and others).

Data never sleeps
PROC Star
Posts: 7,492

Re: Expression for handling two kinds of invalid email adresses in DI Studio

Posted in reply to EinarRoed

Can you use tranwrd?  e.g.:

data have;

  informat email $50.;

  input email &;

  email=tranwrd(tranwrd(email,"@.","@"),"@ ","@");

  cards;

user@.domain.com

user@ domain.com

;

Regular Contributor
Posts: 191

Re: Expression for handling two kinds of invalid email adresses in DI Studio

Posted in reply to EinarRoed

how about using my favorite function tranwrd?:

data email;

length emailin emailout $200;

emailin  = 'user@.domain.com';

emailout = tranwrd(tranwrd(emailin,'@ ','@'),'@.','@');

output;

emailin  = 'user@ domain.com';

emailout = tranwrd(tranwrd(emailin,'@ ','@'),'@.','@');

output;

run;

Regards Fredrik

Ask a Question
Discussion stats
  • 3 replies
  • 269 views
  • 3 likes
  • 4 in conversation