## Concatenate multiple rows into a single value

Solved
Occasional Contributor
Posts: 10

# Concatenate multiple rows into a single value

Hi,

I'd like to know how to create a new column with the concatenation of other column, but with the output below.

If I have the input:

Number Letter

1              a

2              a

3              b

4              c

5              c

6              c

...

I'd like to have this output:

Number Letter   Concatenate

1              a         1, 2

2              a         1, 2

3              b         3

4              c         4, 5, 6

5              c         4, 5, 6

6              c         4, 5, 6

...

MD

Accepted Solutions
Solution
‎01-27-2016 12:31 PM
Super User
Posts: 6,782

## Re: Concatenate multiple rows into a single value

If the data are not already sorted, sort them:

proc sort data=have;

by letter;

run;

data want;

length concatenate \$ 50;

do until (last.letter);

set have;

by letter;

concatenate = catx(', ', concatenate, number);

end;

do until (last.letter);

set have;

by letter;

output;

end;

run;

There are issues that could arise that might require tweaking.  For example, what should happen if a number is missing?  What should happen if there are duplicate numbers?  But the overall approach should work,.

Good luck.

All Replies
Super Contributor
Posts: 490

## Re: Concatenate multiple rows into a single value

One way to do it

``````data want;
set have;
length want \$ 20;
retain want;
by letter;
if first.letter then want = catc(Number);
else want = catc(want,number);
run;

proc sort data =want;
by descending number letter; run;

data want;
set want;
retain x;
by descending letter;
if first.letter then x=want;
else want=x;
drop x;
run;

proc sort data=want; by number; run;``````
Solution
‎01-27-2016 12:31 PM
Super User
Posts: 6,782

## Re: Concatenate multiple rows into a single value

If the data are not already sorted, sort them:

proc sort data=have;

by letter;

run;

data want;

length concatenate \$ 50;

do until (last.letter);

set have;

by letter;

concatenate = catx(', ', concatenate, number);

end;

do until (last.letter);

set have;

by letter;

output;

end;

run;

There are issues that could arise that might require tweaking.  For example, what should happen if a number is missing?  What should happen if there are duplicate numbers?  But the overall approach should work,.

Good luck.

Occasional Contributor
Posts: 10

## Re: Concatenate multiple rows into a single value

This solved my problem!

Thank you all.

🔒 This topic is solved and locked.