BookmarkSubscribeRSS Feed
EinarRoed
Pyrite | Level 9

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.

3 REPLIES 3
LinusH
Tourmaline | Level 20

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
art297
Opal | Level 21

Can you use tranwrd?  e.g.:

data have;

  informat email $50.;

  input email &;

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

  cards;

user@.domain.com

user@ domain.com

;

FredrikE
Rhodochrosite | Level 12

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1098 views
  • 3 likes
  • 4 in conversation