How to define optional parameters in a function?

[dscamelo]

Hi,

I'm trying to define a function with an optional parameter. My goal with the function is to return either the MAX or MIN value of a column, given the choice of the parameter. But it could be omitted, and it would default to returning the MAX value.

I'm trying to do something like this:

PROC FCMP OUTLIB=work.func.test;
	FUNCTION get_extreme(table $, column $, type);
		IF type = 1 /* or not informed */
			PROC SQL;
				SELECT MAX(column) INTO :ret FROM table;
			QUIT;
		ELSE
			PROC SQL;
				SELECT MIN(column) INTO :ret FROM table;
			QUIT;			
		RETURN ret;
	ENDSUB;
RUN;

So, assuming I have a simple table, like this:

+---------------+
|  date | value |
+---------------+
| 20820 |  1000 |
| 20821 |   850 |
| 20822 |  1100 |
| 20823 |   900 |
+---------------+

I could use the function and would get these results:

%put %sysfunc(get_extreme(table, date));
/* Output should be 20823 */
%put %sysfunc(get_extreme(table, value, 0));
/* Output should be 850 */
%put %sysfunc(get_extreme(table, date, 1));
/* Output should again be 20823 */

Can anyone help me?

Thanks in advance.

[ballardw · Accepted solution]

Since we determined in your other post https://communities.sas.com/t5/Base-SAS-Programming/Help-getting-the-Max-Value-from-a-table/m-p/4212... that you want to use this to generate a proc sql step that resembles:

proc sql;
   create table example as
   select name, sex, age
   from sashelp.class
   where age= (select max(age) from sashelp.class)
   ;
quit;

for the maximum case then we can define a macro with a parameter to generate the different summary call, max or min this way:

%macro maxmin(table, column, type=1);
   %if &type=1 %then %do;
      (select max(&column) from &table)
   %end;
   %else %do;
      (select min(&column) from &table)
   %end;
%mend;

And use it as

proc sql;
   create table maxexample as
   select name, sex, age
   from sashelp.class
   where age= %maxmin(sashelp.class, age)
   ;
quit;
proc sql;
   create table minexample as
   select name, sex, age
   from sashelp.class
   where age= %maxmin(sashelp.class, age, type=2)
   ;
quit;

Note that anything except a 1 after type= should do the minimum. If you use 1 or do not include type= on the macro call then the result is the maximum. Type is a KEYWORD parameter and to set a value must use type=. Otherwise you'll get an error "more positional parameters found than defined."

[ChrisBrooks]

Just to add to what @ballardw said it is possible to create an FCMP function with a variable number of parameters but I'm not sure it really suits what you want to do - for the following reasons:

1. The variable parameters have to be declared in the function as a numeric array and you'll have to pass at least one value in the array
2. The function code block can only contain data step and a few FCMP specific function calls. It cannot contain Procedure calls so you'll have to write a macro anyway to run your Proc SQL step and call it from inside the function with a run_macro call
3. Parameter passing between the macro and function is a little different than in normal macro calls and has a few "gotchas" that you need to be aware of.

So I would agree that a straight macro is the way to go here - it's a lot simpler, easier to maintain and efficient than using Proc FCMP in this instance (and I speak as fan of Proc FCMP)...

[dscamelo]

Thank you!

[dscamelo]

Thanks a lot, @ballardw. However, this still has one slight problem with my intended use. With it, I have to run a subquery everytime I need to use the variable. Since the table I am going to be using is fairly large, making several subqueries would be time costly.

I would need a way to store it into a variable, such as:

%let largest = %maxmin(sashelp.class, age);

then, I could run the query just once and use the &largest. wherever I needed it.

[ballardw]

---

@dscamelo wrote:

Thanks a lot, @ballardw. However, this still has one slight problem with my intended use. With it, I have to run a subquery everytime I need to use the variable. Since the table I am going to be using is fairly large, making several subqueries would be time costly.

I would need a way to store it into a variable, such as:

%let largest = %maxmin(sashelp.class, age);

then, I could run the query just once and use the &largest. wherever I needed it.

---

There is considerable difference between "I need to insert this line of code" and "I need to create a macro variable".

Here's one way. Note that the macro creates the variable and makes it global so it should be available in most places needed but scope can get tricky if you are doing lots of nested macros. Note the use of put with the -L modifier. That is to create left justified text. You might run into cases where you end up with leading spaces causing problems so this keeps that from happening. This assumes that the values are numeric as Max and Min are somewhat unreliable with character variables. If you actually need more digits then you will need to specify them in the format portion of the Best. the default without the format is 8 characters including decimal points.

Since a macro variable contains text there is always a chance that a comparison with "=" may fail due to insufficient digits. This approach would work most reliably with interger values OR using a comparison that allows for a small range difference to be considered equal.

[Tom]

---

@dscamelo wrote:

Thanks a lot, @ballardw. However, this still has one slight problem with my intended use. With it, I have to run a subquery everytime I need to use the variable. Since the table I am going to be using is fairly large, making several subqueries would be time costly.

I would need a way to store it into a variable, such as:

%let largest = %maxmin(sashelp.class, age);

then, I could run the query just once and use the &largest. wherever I needed it.

---

SAS is not a "functional" language and you are just going to drive yourself nuts trying to treat it as one.  To me it sounds like you want a way to store a value into a macro variable. So instead of trying to shoehorn something into looking like a function just add a parameter to your macro for the name of the macro variable you want to create.  So let's make a general purpose macro that can call any SQL summary function and return the result to a macro variable.

%macro sql_summary
/*-----------------------------------------------------------------------------
Generate summary statistic using PROC SQL.
-----------------------------------------------------------------------------*/
(varname /* Input variable name */
,dsname  /* Input dataset name (def=&syslast) */
,stat    /* Sql summary function name (def=MAX) */
,mvar    /* Output macro variable name (def=sql_summary) */
,format  /* Optional format to use when generating macro variable */
);
%if ^%length(&varname) %then %put ERROR: &sysmacroname requires a variable name. ;
%else %do;
  %if ^%length(&dsname) %then %let dsname=&syslast;
  %if ^%length(&stat) %then %let stat=max ;
  %if ^%length(&mvar) %then %let mvar=sql_summary;
  %if ^%symexist(&mvar) %then %global &mvar ;
  %if %length(&format) %then %let format=format=&format ;
proc sql noprint ;
select &stat(&varname) &format
  into :&mvar trimmed
  from &dsname
;
quit;
%end;
%mend sql_summary ;

Let's try some examples.

747   %sql_summary(age,sashelp.class,mvar=largest);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


748   %put &=largest ;
LARGEST=16
749   %sql_summary(age,sashelp.class,stat=mean,mvar=average);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


750   %put &=average ;
AVERAGE=13.31579
751   %sql_summary(weight*height,sashelp.class,stat=sum,mvar=total,format=comma12.);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


752   %put &=total ;
TOTAL=120,316
753   %sql_summary;
ERROR: SQL_SUMMARY requires a variable name.