Using PROC FCMP functions as an aggregate function for PROC SQL

tomcmacdonald · Posted 05-03-2017 02:34 PM

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 · Posted 05-03-2017 02:40 PM

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

tomcmacdonald · Posted 05-03-2017 02:47 PM

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.