topic Re: Concatenate Unique Values from Columns in SAS Programming

Concatenate Unique Values from Columns

david27 — Thu, 07 Jul 2022 17:01:12 GMT

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;

Re: Concatenate Unique Values from Columns

pink_poodle — Thu, 07 Jul 2022 18:12:13 GMT

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.

Re: Concatenate Unique Values from Columns

david27 — Thu, 07 Jul 2022 18:19:43 GMT

Thank You @pink_poodle for your reply.

But, I also have to handle duplicates.

Re: Concatenate Unique Values from Columns

pink_poodle — Thu, 07 Jul 2022 18:42:09 GMT

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

Re: Concatenate Unique Values from Columns

Ksharp — Fri, 08 Jul 2022 13:21:17 GMT

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;