DATA Step, Macro, Functions and more

Replacement for XMLAGG in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 94
Accepted Solution

Replacement for XMLAGG in SAS

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!


Accepted Solutions
Solution
‎12-11-2017 06:41 PM
Super User
Posts: 8,220

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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


All Replies
Super User
Posts: 24,026

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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.

Solution
‎12-11-2017 06:41 PM
Super User
Posts: 8,220

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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

 

Super User
Posts: 24,026

Re: Replacement for XMLAGG in SAS

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

Frequent Contributor
Posts: 94

Re: Replacement for XMLAGG in SAS

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?
Super User
Posts: 8,220

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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

Frequent Contributor
Posts: 94

Re: Replacement for XMLAGG in SAS

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

Super User
Posts: 8,220

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 94

Re: Replacement for XMLAGG in SAS

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.
Super User
Posts: 8,220

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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

 

Super User
Posts: 13,942

Re: Replacement for XMLAGG in SAS

Posted in reply to Kalai2008

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 235 views
  • 1 like
  • 4 in conversation