topic Re: Concatenate characters across rows in Enterprise Guide in SAS Enterprise Guide

Concatenate characters across rows in Enterprise Guide

choly1 — Thu, 04 Oct 2012 20:34:25 GMT

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

Re: Concatenate characters across rows in Enterprise Guide

RichardinOz — Fri, 05 Oct 2012 03:59:10 GMT

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 ;

Re: Concatenate characters across rows in Enterprise Guide

choly1 — Mon, 08 Oct 2012 17:53:19 GMT

Thank you so much, it worked!