Concatenate characters across rows in Enterprise Guide

[choly1]

Good afternoon:

I am trying to concatenate characters from different rows as per example below:

Starting table:

Patient_ID         Diagnoses

123456             463.0

123456             472.9

123456             V10.0                       

Finishing table:

Patient ID          Concatenation_Of_Diagnoses

123456              463.0; 472.9; V10.0

I have been splitting tables and using CATX, but if there are a lot of different diagnoses, writing the CATX function can take a long time and it doesn't seem to be an efficient process. Is there a better way to do that or a function in EG that allows to do that?

Many thanks,
Chantal

[RichardinOz · Accepted solution]

CATX is the right function to use but you do not need to split the tables.

Try this in a code segment (might need tweaking because I can't test it).

Data needs to be pre-sorted by Patient_Id.

Data Combined_Diagnoses  (Drop = Diagnoses) ;

    Set Individual_Diagnoses ;

    By  Patient_Id ;

    *   Length needs to be 8x max number of diagnoses ;

    *   Or even longer in case of unexpected number ;

    Length Concatenation_Of_Diagnoses $ 200 ;

    Retain Concatenation_Of_Diagnoses ;

    *   Set initial value per patient to blank ;

    If  First.Patient_Id

      Then Concatenation_Of_Diagnoses = ' ' ;

  Concatenation_Of_Diagnoses = CATX ('; ',

                               Concatenation_Of_Diagnoses, Diagnoses) ;

    If  Last.Patient_Id

      Then Output ;

Run ;

[choly1]

Thank you so much, it worked!