topic Re: Merge the comments based on ID and date in SAS Programming

Merge the comments based on ID and date

Sathish_jammy — Tue, 28 Apr 2020 05:31:59 GMT

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

Re: Merge the comments based on ID and date

Kurt_Bremser — Tue, 28 Apr 2020 05:41:40 GMT

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.

Re: Merge the comments based on ID and date

andreas_lds — Tue, 28 Apr 2020 05:48:35 GMT

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

Re: Merge the comments based on ID and date

Jagadishkatam — Tue, 28 Apr 2020 07:07:23 GMT

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;