Desktop productivity for business analysts and programmers

Concatenate characters across rows in Enterprise Guide

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Concatenate characters across rows in Enterprise Guide

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


Accepted Solutions
Solution
‎10-04-2012 11:59 PM
Super Contributor
Posts: 644

Re: Concatenate characters across rows in Enterprise Guide

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 ;

View solution in original post


All Replies
Solution
‎10-04-2012 11:59 PM
Super Contributor
Posts: 644

Re: Concatenate characters across rows in Enterprise Guide

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 ;

New Contributor
Posts: 2

Re: Concatenate characters across rows in Enterprise Guide

Thank you so much, it worked!

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 2542 views
  • 0 likes
  • 2 in conversation