DATA Step, Macro, Functions and more

data correction (mail id conversion using SAS)

Reply
Contributor
Posts: 46

data correction (mail id conversion using SAS)

Hi All,

Can any one help me to re correct the below data into proper format

Ex:-

XXX@GMMIL.COM           ----------------------  @GMAIL.COM

YYY@GMIL.COM

ZZZ@GMAILL.COM

XXX@YAHOOO.COM     ----------------------   @YAHOO.COM

YYY@YHOO.COM

ZZZ@YAHOHO.COM

XXX@HOOTMAIL.COM   ---------------------- @ HOTMAIL.COM

YYY@HAATMAIL.COM

ZZZ@HITMAIL.COM

I tried to convert into proper format using SPEDIS Function but am not able to get it.

Super Contributor
Posts: 259

Re: data correction (mail id conversion using SAS)

Please post the code you already have.

Relying upon a single function result to alter an EMail-address can cause false replacements.

Just an idea:

data work.want;
   set work.have;
   length
      DomainList $ 100
      Domain $ 20
      i minId minCost cost 8
      NewEMail $ 100
   ;

   /* List of valid domains, if the list is longer: store it in another dataset and use a hash-object */

   retain DomainList "GMAIL.COM YAHOO.COM HOTMAIL.COM";

   /* Anything on the left of @ is unimportant */

   Domain = scan(Email, 2, '@');
   minCost = constant('BIG');

   /*

   do i = 1 to countw(DomainList, ' ');
      cost = spedis(Domain, scan(DomainList, i, ' '));

      if cost < minCost then do;
         minCost = cost;
         minId = i;
      end;
   end;

   NewEMail = catx('@', scan(Email, 1, '@'), scan(DomainList, minId, ' '));
run;

Super User
Super User
Posts: 7,392

Re: data correction (mail id conversion using SAS)

Hi,

Well, if the first letter is always indicative, then just check that:

data have;
  length email $200.;
  input email $;
cards;
XXX@GMMIL.COM
YYY@GMIL.COM
ZZZ@GMAILL.COM
XXX@YAHOOO.COM
YYY@YHOO.COM
ZZZ@YAHOHO.COM
XXX@HOOTMAIL.COM
YYY@HAATMAIL.COM
ZZZ@HITMAIL.COM
;
run;

data want;
  length newmail $200.;
  set have;
  select(substr(scan(scan(email,2,'@'),1,'.'),1,1));
    when ('G') newmail=scan(email,1,'@')||"@GMAIL.COM";
    when ('Y') newmail=scan(email,1,'@')||"@YAHOO.COM";
    when ('H') newmail=scan(email,1,'@')||"@HOTMAIL.COM";
    otherwise newmail="";
  end;
run;

Ask a Question
Discussion stats
  • 2 replies
  • 327 views
  • 6 likes
  • 3 in conversation