topic Re: Concatenate if duplicate in SAS Programming

Concatenate if duplicate

nickspencer — Thu, 07 Jun 2018 02:07:32 GMT

I have a SAS dataset with columns ID(number), Name(Character) and other columns.

ID Name State

1 John CA

2 Amy FL

3 Tim MD

4 Seth NM

5 John VA

If any of the name in the name column repeats or same name occurs more than once,then it should concatenate with the ID in the name column as below in the new dataset.

ID Name State

1 John1 CA

2 Amy FL

3 Tim MD

4 Seth NM

5 John5 VA

What might be the best way to do this?

Thanks

Re: Concatenate if duplicate

ChrisNZ — Thu, 07 Jun 2018 10:43:44 GMT

Like this?

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 DUPLICATES as 
  select NAME 
  from HAVE 
  group by NAME
  having count(NAME) > 1;
quit;

data WANT;
  set HAVE;
  if _N_=1 then do;
    dcl hash DUPLICATES(dataset:'DUPLICATES');
    DUPLICATES.definekey('NAME');
    DUPLICATES.definedone();
  end;
  if DUPLICATES.check()=0 then NAME=catt(NAME,ID);
run;

ID Name State
1 John1 CA
2 Amy FL
3 Tim MD
4 Seth NM
5 John5 VA

[Edit: no need for 2 steps, I started with the assumption that you wanted

NAME=catt(NAME,_N_);

I realised it was

NAME=catt(NAME,ID);

but did not change the code.

So use one of the other single-steps solutions proposed.]

Re: Concatenate if duplicate

novinosrin — Thu, 07 Jun 2018 03:48:25 GMT

data HAVE;
input ID      Name $        State $;
cards;
1        John            CA
2        Amy             FL
3        Tim             MD
4        Seth            NM
5        John            VA
run; 

data want;
  if _N_=1 then do;
  if 0 then set have;
    declare hash h(dataset:'have',multidata:'y');
    h.definekey('NAME');
    h.definedone();
  end;
set have;
if h.check()=0 then do;
count=1;
 h.has_next(result: r);
 if r ne 0 then count+1;
 if count>1 then NAME=catt(NAME,ID);
 end;
 drop count r;
 run;

Re: Concatenate if duplicate

Jagadishkatam — Thu, 07 Jun 2018 03:56:05 GMT

data have;
input ID      Name$         State$;
cards;
1        John            CA
2        Amy             FL
3        Tim              MD
4        Seth             NM
5        John             VA
;

proc sort data=have;
by name;
run;

data want;
do until(last.name);
set have;
by name;
retain cnt;
if first.name then cnt=1;
else cnt+1;
end;
do until(last.name);
set have;
by name;
if cnt>1 then name=cats(name,put(id,best.));
output;
end;
run;

Re: Concatenate if duplicate

novinosrin — Thu, 07 Jun 2018 04:12:24 GMT

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;

Re: Concatenate if duplicate

novinosrin — Thu, 07 Jun 2018 04:21:24 GMT

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;

Re: Concatenate if duplicate

PGStats — Thu, 07 Jun 2018 05:19:07 GMT

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;

Re: Concatenate if duplicate

PGStats — Thu, 07 Jun 2018 06:38:12 GMT

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;

Re: Concatenate if duplicate

Haikuo — Thu, 07 Jun 2018 13:21:55 GMT

No need for a 'count' variable in Hash setting:

data want_h;
 if _n_=1 then do;
   declare hash h(dataset:'have(keep=name)', multidata:'y');
   h.definekey('name');
   h.definedata(all:'y');
   h.definedone();
   end;

   set have;
   h.find(); 
   h.has_next(result:_n_); 
   if _n_ ne 0 then name=cats(name,id);

run;

Re: Concatenate if duplicate

novinosrin — Thu, 07 Jun 2018 14:01:38 GMT

@Haikuo Right, Thank you and a neat catch. Can't believe that didn't cross my mind. I always lack attention to details. 😞

Re: Concatenate if duplicate

nickspencer — Sun, 10 Jun 2018 20:23:54 GMT

Thank you. This is really helpful. But my requirement is changed a little bit. A Teradata table has the following which can be used as a reference.

ID Name State

1 John CA

2 Amy FL

3 Tim MD

4 Seth NM

5 John VA

6 Amy NM

So, if my dataset has any of the names which have occurred more than once in the above reference table. Then it should concatenate. It may not be repeating in the dataset.

ID Name

5 John

4 Seth

3 Tim

6 Amy

The output should be

ID Name

5 John (5)

4 Seth

3 Tim

6 Amy (6)

Thanks in Advance.

Re: Concatenate if duplicate

nickspencer — Sun, 10 Jun 2018 20:28:58 GMT

Thank you. This is really helpful. But my requirement is changed a little bit. A Teradata table has the following which can be used as a reference.

ID Name State

1 John CA

2 Amy FL

3 Tim MD

4 Seth NM

5 John VA

6 Amy NM

So, if my dataset has any of the names which have occurred more than once in the above reference table. Then it should concatenate. It may not be repeating in the dataset.

ID Name

5 John

4 Seth

3 Tim

6 Amy

The output should be

ID Name

5 John (5)

4 Seth

3 Tim

6 Amy (6)

Thanks in Advance.

Re: Concatenate if duplicate

Patrick — Mon, 11 Jun 2018 01:56:39 GMT

@nickspencer

Given your Have data and your desired result this appears to be mainly a question of "How to select the row with the same name and the highest ID?". Is that correct??

If so then why do you want to concatenate the ID to the name as well? Isn't that information you've got already in the ID column?

I also assume that you would want code which executes in-database? Right?

Re: Concatenate if duplicate

ChrisNZ — Mon, 11 Jun 2018 02:21:40 GMT

Like this?


data REF;
input ID      Name $        State $;
cards;
1        John            CA
2        Tim             MD
3        Amy             FL
4        Seth            NM
5        John            VA
6        Amy             FL
run; 
 
data HAVE;
input ID      Name $ ;
cards;
2        Tim         
4        Seth        
5        John        
6        Amy         
run; 
 
proc sql; 
  create table WANT as 
  select have.ID
        ,catx(' ',have.NAME,ifc(dup.NAME ne ' ', catt('(',ID,')'),' ')) as NAME
  from HAVE 
         left join 
      (select NAME from REF group by NAME having count(NAME) > 1) dup
        on have.NAME=dup.NAME ;
quit;

ID NAME
6 Amy (6)
5 John (5)
4 Seth
2 Tim

Re: Concatenate if duplicate

ChrisNZ — Mon, 11 Jun 2018 02:24:26 GMT

If you want the code on the reference table to execute in database,

create an intermediate table for

select NAME from REF group by NAME having count(NAME) > 1

I doubt SAS will pass it as is, but then maybe it will.

Re: Concatenate if duplicate

nickspencer — Mon, 11 Jun 2018 03:06:33 GMT

Hi ChrisNZ,

This works great but the 'have' dataset has about 50 other variables which maynot be convenient to include all in the select clause. Is there any way so I don't have to include all variables in the select clause?

Thanks.

Re: Concatenate if duplicate

ChrisNZ — Mon, 11 Jun 2018 03:16:09 GMT

> Is there any way so I don't have to include all variables in the select clause?

You are really hard to follow. The 50 variables are not included. Only 2 variables are.

Re: Concatenate if duplicate

ChrisNZ — Mon, 11 Jun 2018 03:21:08 GMT

Unless you mean you want to *include* but not to explicitly *list*?

Like this?

 create table WANT(drop=NAME rename=(NAME2=NAME)) as 
  select have.*
        ,catx(' ',have.NAME,ifc(dup.NAME ne ' ', catt('(',ID,')'),' ')) as NAME2

Please try to be precise in your questions and explanations. It will help you greatly.

Throwing a few words together as they come is seldom right the first time. Treat these pages as a school essay, not a shopping list.

Re: Concatenate if duplicate

nickspencer — Mon, 11 Jun 2018 03:30:36 GMT

That was what I was trying to say. Thanks for your suggestion.