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
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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.