Catx to reshape data horizontally

[buechler66]

Unlike a similar question you guys just helped me with - about how to de-dup a list of comma delimited values stored in a variable.  My challenge is time is to find a method to prevent the CATX function from ever putting a duplicate date value into the list to begin with.  If you have any thoughts on how to alter the code below to accomplish this I'd really appreciate the insights.

The following piece of SAS code takes data rows that looks like this:

BENE   FROM_DT (stored in a SAS dataset as SAS date values)

12345  23OCT2010

12345  11NOV2015

12345  11NOV2015

12345  15DEC2011

12345  11NOV2015

And changes the multiple rows into a single row by Bene with the dates all transformed into a single variable named From_Date_List using the CATX function:

BENE   FROM_DT_LIST

12345  23OCT2010, 11NOV2015, 11NOV2015, 15DEC2011, 11NOV2015 

PROC SORT DATA=FROM_DT_RESHAPE; BY BENE_SK; RUN;

DATA FROM_DT_RESHAPED;
LENGTH FROM_DT_LIST $1000;
DO UNTIL (LAST.BENE);
SET FROM_DT_RESHAPED;
BY BENE;
FROM_DT_LIST = CATX(', ',FROM_DT_LIST, PUT(FROM_DT, DATE9.));
END;
KEEP FROM_DT_LIST BENE;
RUN;

[Reeza]

Do your dates have any order? It doesn't in your demo, but if it does in real life it will help if you can add another BY variable. Also, why are you doing this? I suspect there's an alternative that's easier, ie Transpose and retranspose.

[buechler66]

No, the dates have no specific order. This is my required method of accomplishing the task so Transpose isn't' an option for me. Thanks tho.

[novinosrin]

What's your expected output? BENE FROM_DT_LIST 12345 23OCT2010, 11NOV2015, 11NOV2015, 15DEC2011, 11NOV2015

[buechler66]

The expected output would be (dates in any order):

BENE FROM_DT_LIST

12345 23OCT2010, 11NOV2015, 15DEC2011

[novinosrin · Accepted solution]

ok, that's easy

data have;
input BENE   FROM_DT :date9.; 
format from_dt date9.;
cards;
12345  23OCT2010
12345  11NOV2015
12345  11NOV2015
12345  15DEC2011
12345  11NOV2015
;


data want;
array t(999) $9 _temporary_;
call missing(of t(*),n);
do until(last.bene);
set have;
by bene;
temp=put(from_dt,date9.);
if temp not in t then do;
n+1;
t(n)=temp;
end;
end;
want_list=catx(', ',of t(*));
keep bene want_list;
run;

[buechler66]

Thanks so much for taking the time to help. I really appreciate it.