Merge the comments based on ID and date

[Sathish_jammy]

Dear Experts,

I have to merge the comments to one row for the duplicate IDs, I ll give you the sample set with expected output.

data aaa;
Input ID Date mmddyy10. Remarks$;
format Date mmddyy10.;
cards;
123 05/05/2002 comment1
123 05/05/2002 comment2
123 08/07/2003 comment1
145 03/17/2005 comment1
145 03/17/2005 comment2
145 10/12/2006 comment1
;

Expected Output

123 05/05/2002 comment1, comment2

123 08/07/2003 comment1

145 03/17/2005 comment1, comment2

145 10/12/2006 comment1

[Kurt_Bremser]

In a data step, do

by id date;

Define a new character variable (e.g. remarks) with sufficient length. At first.date, set it to remark, otherwise do

remarks = catx(',',remarks,remark);

Then use a subsetting if at last.date to only keep one observation per id and date.

[andreas_lds]

Idea:

• sort "aaa" by Id and Date, then use set+by in a data step
• you will have to create a new variable to hold all remarks, calculating the length could be interesting
• join the remarks with catx (as shown by @Kurt_Bremser)
• use last.date and output to control which obs is written to the dataset

[Jagadishkatam · Accepted solution]

please try the below code

data aaa;
Input ID Date mmddyy10. Remarks$;
format Date mmddyy10.;
cards;
123 05/05/2002 comment1
123 05/05/2002 comment2
123 08/07/2003 comment1
145 03/17/2005 comment1
145 03/17/2005 comment2
145 10/12/2006 comment1
;

data want;
length newvar $200.;
set aaa;
by id date notsorted;
retain newvar;
if first.date then newvar=remarks;
else newvar=catx(',',newvar,remarks);
if last.date;
run;