BookmarkSubscribeRSS Feed
maverickkub
Obsidian | Level 7

Hi,

I am trying to clean a Customer related data which is in excel format.Basically, what I am trying to achieve is that I am trying to assign multiple contact details from different observations to the same name(the same person has multiple observations with different contact details.

e.g.

Name                Contact                   e-mail

Tim                  +918223378569      tim.allen@gmail.com

Tim                  +343434343434       tim123.@yahoo.com

Tim                  +916544778889       ris_tim@hotmail.com

I want this to be grouped  as a single observation.(and all other observations should follow the same rule ofcourse)

So this is the result that I wish to achieve

Name              Conatct                                                                         email

Tim               +918223378569,+343434343434,+916544778889                 tim.allen@gmail.com,tim123.@yahoo.com,ris_tim@hotmail.com

Thanks in advance.

3 REPLIES 3
user24feb
Barite | Level 11

Try:

Data A;
  Input Name $4. Contact $14. e_mail $20.;
  Datalines;
Tim +918223378569 tim.allen@gmail.com
Tim +343434343434 tim123.@yahoo.com
Tim +916544778889 ris_tim@hotmail.com
BBB +118223378569 tim.allen@gmail.com
BBB +143434343434 tim123.@yahoo.com
BBB +116544778889 ris_tim@hotmail.com
;
Run;

Proc Sort Data=A;
  By Name;
Run;

Data Want;
  Length Contact_Agg $100. e_mail_Agg $100.;
  Retain Contact_Agg e_mail_Agg;
  Do _N_=1 By 1 Until (Last.Name);
    Set a;
By Name;
Contact_Agg=IfC(First.Name,Contact,CatX(',',Contact_Agg,Contact));
e_mail_Agg=IfC(First.Name,e_mail,CatX(',',e_mail_Agg,e_mail_Agg));
  End;
Run;

Kurt_Bremser
Super User

Sort by Name.

Then do

data want (

  drop=contact email

  rename=(new_contact=contact new_email=email)

);

set have;

by Name;

length new_contact new_email $ 500; * or whatever is needed to hold all values;

retain new_contact new_email;

if first.Name then do;

  new_contact = '';

  new_email = '';

end;

new_contact = catx(',',trim(new_contact),contact);

new_email = catx(',',trim(new_email),email);

if last.Name then output;

run;

LinusH
Tourmaline | Level 20

Is this a one time shot, or are you preparing a more permanent solution?

If so, your proposed solution might be optimal. To search for a specific phon no needs scanning through a variable length variable, which is not query friendly.

Consider a to normalize the table structure instead, either by having separate phone and a email tables, or a common with contact type and the address (phone no or email).

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 995 views
  • 0 likes
  • 4 in conversation