topic Catx to reshape data horizontally in SAS Programming

Catx to reshape data horizontally

buechler66 — Wed, 21 Nov 2018 02:39:27 GMT

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 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;

Re: Catx to reshape data horizontally

Reeza — Wed, 21 Nov 2018 02:46:39 GMT

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.

Re: Catx to reshape data horizontally

novinosrin — Wed, 21 Nov 2018 02:47:07 GMT

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

Re: Catx to reshape data horizontally

buechler66 — Wed, 21 Nov 2018 02:51:10 GMT

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.

Re: Catx to reshape data horizontally

buechler66 — Wed, 21 Nov 2018 02:52:12 GMT

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

BENE FROM_DT_LIST

12345 23OCT2010, 11NOV2015, 15DEC2011

Re: Catx to reshape data horizontally

novinosrin — Wed, 21 Nov 2018 03:12:52 GMT

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;

Re: Catx to reshape data horizontally

buechler66 — Wed, 21 Nov 2018 17:10:38 GMT

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