topic Re: Combining Multiple people staying at same address in SAS Programming

Combining Multiple people staying at same address

Yvegunta — Wed, 21 Mar 2018 04:23:22 GMT

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

Re: Combining Multiple people staying at same address

Astounding — Wed, 21 Mar 2018 04:56:08 GMT

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.

Re: Combining Multiple people staying at same address

DR_Majeti — Wed, 21 Mar 2018 04:49:31 GMT

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

Re: Combining Multiple people staying at same address

PGStats — Wed, 21 Mar 2018 05:21:23 GMT

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;