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
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 ;
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 ;
Thank you so much, it worked!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.