Concatenate multiple rows into a single value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

...

 

Thank you in advance.

MD


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

Re: Concatenate multiple rows into a single value

If the data are not already sorted, sort them:

 

proc sort data=have;

by letter;

run;

 

Then add the new variable:

 

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.

View solution in original post


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: 5,372

Re: Concatenate multiple rows into a single value

If the data are not already sorted, sort them:

 

proc sort data=have;

by letter;

run;

 

Then add the new variable:

 

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 372 views
  • 0 likes
  • 3 in conversation