DATA Step, Macro, Functions and more

Combining Multiple people staying at same address

Reply
Occasional Contributor
Posts: 14

Combining Multiple people staying at same address

Hi All,

 

I am trying to combine the names of the people staying at the same location having the same account_ID, as shown in the table below:

 

NameAddressAccount_ID
ArnoldPitt Street123
StallonePitt Street123
WillisPitt Street123
SmithGeorge Street123

 

What i ideally am looking to get is as below:

 

NameAddressAccount_ID
Arnold and Stallone and WillisPitt Street123
SmithGeorge Street123

 

Thanks in advance.

Yatindra

Super User
Posts: 6,751

Re: Combining Multiple people staying at same address

[ Edited ]

Should be workable:

 

data want;

length newname $ 50;

set have;

by account_ID address;

if first.address then newname = name;

else newname = trim(newname) || ' and ' || name;

if last.address;

retain newname;

drop name;

rename newname = name;

run;

 

*********** EDITED to add the correction.

Contributor
Posts: 72

Re: Combining Multiple people staying at same address

Hi Yatindar, 

 

data have;
infile datalines dsd ;
length Name $200 Address $200;
input Name $ Address $ Account_ID ;

datalines;
Arnold , Pitt Street, 123
Stallone, Pitt Street, 123
Willis, Pitt Street, 123
Smith, George Street, 123
;
run;

proc sort data=have;
by address account_id;
run;

data required;
set have;
by address account_id;
length new_name $200;
retain new_name ' ';
if first.address then new_name=strip(name);
else new_name=strip(new_name)||' and '||strip(name);
name=new_name;
drop new_name;
if last.address;
run;

 

Please find the solution

Esteemed Advisor
Posts: 5,521

Re: Combining Multiple people staying at same address

Use a do until() loop for this:

 

data have;
input Name :$12. Address &:$24. Account_ID ;
datalines;
Arnold Pitt Street  123 
Stallone Pitt Street  123 
Willis Pitt Street  123 
Smith George Street  123
; 

proc sort data=have; by account_id address name; run;

data want;
length allNames $64;
do until(last.address);
    set have; by account_id address;
    allNames = catx(" and ", allNames, name);
    end;
drop name;
rename allNames = name;
run;

proc print data=want noobs; run;
PG
Ask a Question
Discussion stats
  • 3 replies
  • 104 views
  • 0 likes
  • 4 in conversation