Combining Multiple people staying at same address

[Yvegunta]

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:

Name	Address	Account_ID
Arnold	Pitt Street	123
Stallone	Pitt Street	123
Willis	Pitt Street	123
Smith	George Street	123

What i ideally am looking to get is as below:

Name	Address	Account_ID
Arnold and Stallone and Willis	Pitt Street	123
Smith	George Street	123

Thanks in advance.

Yatindra

[Astounding]

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.

[DR_Majeti]

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

[PGStats]

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;