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

Hello,

 

I was using the XMLAGG function in Oracle and it throws me an error. Would like to try the same in SAS. Need a new column with all the values separated by comma. 

 

Please  note: The product data is huge and hence I got the error in Oracle like

ERROR: ORACLE execute error: ORA-19011: Character string buffer too small.

 

 

Data:

 

ID      Product     

1        x                 

1        y                 

2        s                

2        j

2        k

 

Output:

 

Id   NewColumn

1     x,y

2     s,j,k

 

 

Thank you for looking!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

While SAS doesn't have the function, you can easily do it using SAS. e.g.:

data want (drop=product);
  set have;
  by id;
  retain NewColumn;
  if first.id then NewColumn=product;
  else NewColumn=catx(',',NewColumn,product);
  if last.id then output;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

10 REPLIES 10
Reeza
Super User

If you can use the AGG function via SQL pass through that's your best option. 

If you can't, another option is to transpose the data and then use CATX or you can use a data step and loop through it. 

 

This question was asked recently regarding the AGG type functionality and unfortunately SAS doesn't have it implemented at the moment. There is a ballot ware item to have it added.

art297
Opal | Level 21

While SAS doesn't have the function, you can easily do it using SAS. e.g.:

data want (drop=product);
  set have;
  by id;
  retain NewColumn;
  if first.id then NewColumn=product;
  else NewColumn=catx(',',NewColumn,product);
  if last.id then output;
run;

Art, CEO, AnalystFinder.com

 

Reeza
Super User

@art297 solution assumes the data is pre-sorted. In a big data set I suspect that assumption is invalid. If it is, then it's relatively easy to aggregate what you need. 

Kalai2008
Pyrite | Level 9
Its not concatenating all the products. Each product size is 23 character. So there are 14 products and concatenated only 6 products. Do you know the reason?
art297
Opal | Level 21

You probably have to preset the necessary length. e.g.:

data want (drop=product);
  set have;
  length NewColumn $350;
  by id;
  retain NewColumn;
  if first.id then NewColumn=product;
  else NewColumn=catx(',',NewColumn,product);
  if last.id then output;
run;

Art, CEO, AnalystFinder.com

Kalai2008
Pyrite | Level 9

Yes, I already added a length statement to 500. Thank you!

art297
Opal | Level 21

And did that correct the problem? If not, post your code.

 

Art, CEO, AnalystFinder.com

 

Kalai2008
Pyrite | Level 9
The product Number looks like this 'ABCD0000000345789479607'. Each customer (ID) have several Product Numbers. One customer can have 1 to 5000 product numbers. I used the same query and increased the length to 32767 (max). 23*5000=115,000. I don't think we can increase the length. The same reason oracle throwed me error in XMLAGG function.
art297
Opal | Level 21

Do you need to end up with the non-attainable extra long string or would something like the following suffice:

data want (drop=product);
  set have;
  array NewColumn(5000) $14.;
  by id;
  retain i NewColumn:;
  if first.id then do;
    call missing(of NewColumn(*));
    i=1;
  end;
  else i+1;
  NewColumn(i)=product;
  if last.id then output;
run;

Art, CEO, AnalystFinder.com

 

ballardw
Super User

How long did you make the LENGTH of the target variable? If a variable is to hold a comma delimited list it would need 26*14 characters for the product and an additional 13 for commas between 14 of them. Total 335. Less than that is likely not to hold any group requiring all 14 variables.

 

Likely the length of your resulting variable is around 160 OR you getting lots of blanks carried around for some reason.

 

It never hurts to show the actual code you ran as we can't see anything you don't share and have to ask questions that would be answered by the code.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 10 replies
  • 1959 views
  • 1 like
  • 4 in conversation