DATA Step, Macro, Functions and more

Using PROC FCMP functions as an aggregate function for PROC SQL

Reply
Occasional Contributor
Posts: 15

Using PROC FCMP functions as an aggregate function for PROC SQL

[ Edited ]

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

Trusted Advisor
Posts: 1,022

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

Posted in reply to tomcmacdonald

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;

Occasional Contributor
Posts: 15

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

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.
Respected Advisor
Posts: 4,925

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

Posted in reply to tomcmacdonald

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

PG
Ask a Question
Discussion stats
  • 3 replies
  • 166 views
  • 1 like
  • 3 in conversation