Hi to everyone,
I have similar the data below;
MUS_NO SERVICE
22222 a
22222 b
22222 b
11111 x
11111 y
33333 a
33333 b
33333 c
33333 d
33333 e
and i'd like to convert into these table;
MUST_NO list_service
11111 x,y
22222 a,b,b
33333 a,b,c,d,e
Actually i could obtain the last table by using transpoze and cat function:
/*sorting before transpoze step*/
PROC SQL;
CREATE TABLE SAS_EXAMPLE AS
SELECT * FROM SAS_EXAMPLE
ORDER BY MUS_NO ASC,SERVICE ASC;
QUIT;
/*transpozing by mus_no*/
PROC TRANSPOZE DATA=SAS_EXAMPLE OUT=SAS_EXAMPLE_2 PREFIX=col;
BY MUS_NO;
VAR SERVICE;
RUN;
/*combining transpozed columns with comma and deleting unnecessary columns*/
DATA SAS_EXAMPLE_3;
SET SAS_EXAMPLE_2;
LENGTH list_service $200;
list_service=catx(',',of col:);
DROP _NAME_ col1--col5/*should write max number of obs for a must_no. For this case 5 for mus_no 33333*/;
RUN;
However, I have thousands rows for a customer so i donot want to use transpoze procedure so i tried to use these code:
data list;
set WORK.QUERY_FOR_SAS_EXAMPLE_LIST;
by mus_no;
retain list_service;
if first.mus_no then list_service_k=1; else list_service_k=0;
if first.mus_no then list_service=service;
list_service_a = catx(",",service,list_service);
list_service=list_service_a;
run;
But i can't. How can i obtain this table i want?
Thank you!
You need to make the target variable large enough; keep in mind that even with the maximum size of character variables, you may not be able to hold all values given that you have "thousands" of rows per customer:
data want;
set have;
by mus_no notsorted;
length list_service $32767;
retain list_service;
if first.mus_no then list_service = "";
list_service = catx(",",list_service,service);
if last.mus_no;
keep mus_no list_service;
run;
The question arises: what for do you need this monster string?
You need to make the target variable large enough; keep in mind that even with the maximum size of character variables, you may not be able to hold all values given that you have "thousands" of rows per customer:
data want;
set have;
by mus_no notsorted;
length list_service $32767;
retain list_service;
if first.mus_no then list_service = "";
list_service = catx(",",list_service,service);
if last.mus_no;
keep mus_no list_service;
run;
The question arises: what for do you need this monster string?
thank you very much!
try this:
data have;
input MUS_NO SERVICE $;
cards;
22222 a
22222 b
22222 b
11111 x
11111 y
33333 a
33333 b
33333 c
33333 d
33333 e
;
run;
proc sort data = have;
by MUS_NO SERVICE;
run;
data want;
do until(last.MUS_NO);
set have;
by MUS_NO;
length list_service $ 100;
list_service = catx(",", list_service, SERVICE);
end;
output;
keep MUS_NO list_service;
run;
proc print;
run;
Bart
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.