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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.