DATA Step, Macro, Functions and more

Using PROC FCMP functions as an aggregate function for PROC SQL

Reply
Occasional Contributor
Posts: 6

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

Valued Guide
Posts: 797

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

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: 6

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,651

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

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
  • 144 views
  • 1 like
  • 3 in conversation