topic Re: Using PROC FCMP functions as an aggregate function for PROC SQL in SAS Programming

Using PROC FCMP functions as an aggregate function for PROC SQL

tomcmacdonald — Wed, 03 May 2017 18:36:59 GMT

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

Re: Using PROC FCMP functions as an aggregate function for PROC SQL

mkeintz — Wed, 03 May 2017 18:40:22 GMT

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;

Re: Using PROC FCMP functions as an aggregate function for PROC SQL

tomcmacdonald — Wed, 03 May 2017 18:47:21 GMT

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.

Re: Using PROC FCMP functions as an aggregate function for PROC SQL

PGStats — Wed, 03 May 2017 20:43:38 GMT

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