BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
senfonik
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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?

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Fluorite | Level 6

thank you very much!

yabwon
Onyx | Level 15

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

 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



senfonik
Fluorite | Level 6
thank you very much!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 951 views
  • 2 likes
  • 3 in conversation