Concatenate Unique Values from Columns

[david27]

Hello,

Below is what I HAVE and what I WANT

The problem that i have is the delimiter. I have COMMA inside the column value and also after concatenation, I need to have comma as a separator.

In my actual data, these are First Names and Last Names. So if a person's name is repeated, I keep that person's name only once.

Please advise.

data have;
infile datalines dlm="|";
input id n1 :$4. n2 :$4. n3 :$4. n4 :$4.;
datalines;
1|a,b| |a,b|d
2|a| | | 
3|a,b| |b,d|d
4| |b| |d
;
run;

data want;
infile datalines dlm="|";
input id final $16.;
datalines;
1|a,b , d
2|a 
3|a,b , b,d , d
4|b , d
;
run;

[pink_poodle]

Before concatenating, replace the commas within the columns with something else (e.g., letter “x”). There is a REPLACE function for that. Then replace “x” back to a comma in the want dataset.

[david27]

Thank You @pink_poodle  for your reply.

But, I also have to handle duplicates.

[pink_poodle]

I see. You can transpose by name. That will make rows with concatenated values into columns. After that, you can concatenate the resulting columns.

[Ksharp · Accepted solution]

data have;
infile datalines dlm="|";
input id n1 :$4. n2 :$4. n3 :$4. n4 :$4.;
datalines;
1|a,b| |a,b|d
2|a| | | 
3|a,b| |b,d|d
4| |b| |d
;
run;

data want;
 set have;
 length final $ 200;
 array x{*} $ n1-n4;
 do i=1 to dim(x);
  if not findw(final,strip(x{i}),'|') then final=catx('|',final,x{i});
 end;
 final=tranwrd(final,'|',' , ');
 keep id final;
run;