topic PLSQL-like functions in PROC SQL or PROC FEDSQL? in SAS Programming

PLSQL-like functions in PROC SQL or PROC FEDSQL?

tomcmacdonald — Fri, 12 Jan 2018 20:16:14 GMT

I would like to be able to recreate the functionality of PLSQL functions in SAS. Specifically a function that returns a table from a set of parameters. Something like:

PROC SQL;
  SELECT *
  FROM myfunction(&param1, &param2,...,&paramn);
QUIT;

Would this be possible with PROC DS2 or PROC FCMP?

Would an aggregation function be possible? For instance I have a column of strings with another column to indicate group and I want to concatenate them based on the group column. Something like:

PROC SQL;
  SELECT myaggregationfunction(STRING_COLUMN)
  FROM mytable
  GROUP BY GROUP_COLUMN;
QUIT;

Re: PLSQL-like functions in PROC SQL or PROC FEDSQL?

SASKiwi — Fri, 12 Jan 2018 22:05:27 GMT

What is your use case? There may be a better way to approach your problem than in SQL. In Oracle you don't have so much choice, but in SAS you do.

Re: PLSQL-like functions in PROC SQL or PROC FEDSQL?

PGStats — Fri, 12 Jan 2018 23:02:29 GMT

Group concatenation is also on other SAS users wish list. Please check the following SASware Ballot idea:

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

and vote for it!