How to all values separated by comma in one row by id

[senfonik]

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!

[Kurt_Bremser · Accepted solution]

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?

[senfonik]

thank you very much!

[yabwon]

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

[senfonik]

thank you very much!