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
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;
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.
Idea:
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.