topic Re: Replacement for XMLAGG in SAS in SAS Programming

Replacement for XMLAGG in SAS

Kalai2008 — Mon, 11 Dec 2017 22:51:38 GMT

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!

Re: Replacement for XMLAGG in SAS

Reeza — Mon, 11 Dec 2017 22:57:51 GMT

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.

Re: Replacement for XMLAGG in SAS

art297 — Mon, 11 Dec 2017 23:07:02 GMT

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

Re: Replacement for XMLAGG in SAS

Reeza — Mon, 11 Dec 2017 23:19:24 GMT

@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.

Re: Replacement for XMLAGG in SAS

Kalai2008 — Mon, 11 Dec 2017 23:59:57 GMT

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?

Re: Replacement for XMLAGG in SAS

art297 — Tue, 12 Dec 2017 00:14:38 GMT

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

Re: Replacement for XMLAGG in SAS

ballardw — Tue, 12 Dec 2017 00:17:32 GMT

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.

Re: Replacement for XMLAGG in SAS

Kalai2008 — Tue, 12 Dec 2017 00:37:49 GMT

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

Re: Replacement for XMLAGG in SAS

art297 — Tue, 12 Dec 2017 00:44:48 GMT

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

Art, CEO, AnalystFinder.com

Re: Replacement for XMLAGG in SAS

Kalai2008 — Tue, 12 Dec 2017 03:48:09 GMT

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.

Re: Replacement for XMLAGG in SAS

art297 — Tue, 12 Dec 2017 04:17:08 GMT

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