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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.