Using PROC FCMP functions as an aggregate function for PROC SQL

[tomcmacdonald]

Let's say I have this dateset:

DATA foo;
	input a b $;
	datalines;
1 apple
1 banana
1 orange
2 grape
2 kiwi
3 strawberry
;
RUN

And I want to concatenate b grouping by a using PROC SQL.  Maybe something like this:

PROC SQL;
	select
		concatenate(b)
	from
		foo
	group by
		a;
QUIT;

How would I go about doing that with PROC FCMP?

Thank you

[mkeintz]

I'm sure one could torture PROC FCMP enough to create the function that you want.  But because you want a function over several observations it will probably be very messy.  Given your example, how about this data step, using the CATX function and a "SET ... BY" pair of statements inside a "do until last." loop?

DATA foo;
input a b $10.;
datalines;
1 apple
1 banana
1 orange
2 grape
2 kiwi
3 strawberry
;
RUN;

data want;

  do until (last.a);

    set foo;

    by a;

    length all_b_values $60;

    all_b_values=catx(',',all_b_values,b);

  end;

run;

[tomcmacdonald]

Not a fan of the data step but it seems unavoidable here. All of the CAT functions don't work as aggregate functions in PROC SQL. Thanks for the reply.

[PGStats]

Please vote for adding string aggregate functions to SAS/SQL at:

https://communities.sas.com/t5/SASware-Ballot-Ideas/create-string-summary-functions/idi-p/288035