data HAVE;
input ID Name $ State $;
cards;
1 John CA
2 Amy FL
3 Tim MD
4 Seth NM
5 John VA
run;
proc sql;
create table want as
select id,case when c>1 then catt(a.NAME,ID) else a.name end as name,state
from have a, (select name, count(name) as c from have group by name) b
where a.name=b.name;
quit;
data HAVE;
input ID Name $ State $;
cards;
1 John CA
2 Amy FL
3 Tim MD
4 Seth NM
5 John VA
run;
proc sql;
create table want as
select id, case when count(name)>1 then catt(NAME,ID) else name end as name,state
from have
group by name
order by id;
quit;
Yet another SQL solution:
proc sql;
create table duplicates as
select name from have group by name having count(*) > 1;
update have
set name = cats(name,id)
where name in (select name from duplicates);
drop table duplicates;
quit;
A hash with suminc could also be used:
data want;
length count incr 8 name $8;
if _n_ = 1 then do;
dcl hash h(suminc:"incr");
h.definekey('name');
h.definedone();
incr = 1;
do until (done);
set have end=done;
if h.check() ne 0 then h.add();
end;
end;
incr = 0;
set have;
if h.sum(sum:count) eq 0 then
if count > 1 then name = cats(name,id);
drop incr count;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.