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!
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
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.
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
@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.
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
Yes, I already added a length statement to 500. Thank you!
And did that correct the problem? If not, post your code.
Art, CEO, AnalystFinder.com
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.