topic Re: Concatenating strings from multiple rows into single row in table in SAS Programming

Concatenating strings from multiple rows into single row in table

jos283 — Fri, 21 Jul 2017 00:49:05 GMT

I have the following dataset called Results_Before

Observation Fruit ID Fruit Color Shape

1 1 Banana Yellow Odd

2 2 Apple Red Circle_1

3 2 Apple Green Circle_2

4 3 Orange Orange Circle

5 4 Watermelon Green Oval

6 5 Berry Blue Odd_1

7 5 Berry Red Odd_2

8 5 Berry Purple Odd_3

9 6 Peach Peach Circle

How would you code to concatenate the columns using Data Step so that I get the output with 2 new columns below:

As you can see, when there are multiple same fruit numbers, the first color_joined and shape_joined remains the same but each row after adds the previous color and shape to color_joined and shape_joined.

Results_After

Observation Fruit_ID Fruit Color Shape Color_Joined Shape_Joined

1 1 Banana Yellow Odd Yellow Odd

2 2 Apple Red Circle_1 Red Circle_1

3 2 Apple Green Circle_2 Red, Green Circle_1, Circle_2

4 3 Orange Orange Circle Orange Circle

5 4 Watermelon Green Oval Green Oval

6 5 Berry Blue Odd_1 Blue Odd_1

7 5 Berry Red Odd_2 Blue, Red Odd_1, Odd_2

8 5 Berry Purple Odd_3 Blue, Red, Purple Odd_1, Odd_2, Odd_3

9 6 Peach Peach Circle Peach Circle

I tried only for Color to begin with, with the hope of also including Shape into the code.

The code I have so far doesn't work but here it is:

Also, not sure if Lag() function would help here?

Data Results_After;
     Set Results_Before;
           By Observation;
     If First.Fruit_ID then Color_Joined = Color;
     Else Do;
           Color_Joined = catx(',', Color);
     End;
Run;

Proc Print;
Run;

Re: Concatenating strings from multiple rows into single row in table

SuzanneDorinski — Fri, 21 Jul 2017 15:13:45 GMT

data fruit_info;
  length fruit $ 10;
  input observation fruit_id fruit $ color $ shape $;
  datalines;
       1                     1             Banana          Yellow             Odd
       2                     2               Apple            Red              Circle_1
       3                     2               Apple           Green            Circle_2
       4                     3              Orange         Orange           Circle
       5                     4          Watermelon      Green              Oval
       6                     5               Berry            Blue               Odd_1
       7                     5               Berry            Red                Odd_2
       8                     5               Berry           Purple             Odd_3
       9                     6              Peach          Peach              Circle
       ;
run;

proc print data=fruit_info noobs;
  title 'Input data set';
run;

proc sort data=fruit_info;
  by fruit_id;
run;

data fruit_info_retained;
  length color_joined shape_joined $ 50;
  retain color_joined shape_joined;
  set fruit_info;
  by fruit_id;
  if first.fruit_id then
    do;
      color_joined = color;
      shape_joined = shape;
    end;
  else 
    do;
      color_joined = catx(', ', color_joined, color);
      shape_joined = catx(', ', shape_joined, shape);
    end;
run;
  
proc sort data=fruit_info_retained;
  by observation;
run;

proc print data=fruit_info_retained noobs;
  title 'Output data set';
  var observation fruit_id fruit color shape color_joined shape_joined;
run;

Re: Concatenating strings from multiple rows into single row in table

andreas_lds — Fri, 21 Jul 2017 15:14:39 GMT

Just add a retain-statement after by.

retain Color_Joined Shape_Joined;

Re: Concatenating strings from multiple rows into single row in table

Ronein — Fri, 12 Apr 2019 11:00:50 GMT

Hello

Should retain statement be after by or before????

Re: Concatenating strings from multiple rows into single row in table

benhaz — Thu, 28 May 2020 16:30:02 GMT

Hi,

I am trying to concatenate all the rows from each group. I have written a code from which I am getting my desire result in last line(using last.) but problem is , it is only keeping up to 32767 characters. I am looking for a solution, if the character limit reaches 32767 then in the next variable (column) the remaining character should add. I am using the below code but it is not spiting into different variable if limit reaches. I have attached the result. Any help will be appreciated. Many thanks in advance.

data part4 (keep=DOC_NUMBER original_variable count);
set part3;
BY DOC_NUMBER;
if FIRST.DOC_NUMBER then
Count = 0;
Count + 1;
run;

data part5;
length concatenated_field $ 32767;
retain concatenated_field;
set part4;
by DOC_NUMBER;
if first.DOC_NUMBER then
do;
concatenated_field = original_variable;
end;
else
do;
concatenated_field = catx(', ', concatenated_field, original_variable);
end;
run;