BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dscamelo
Obsidian | Level 7

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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."

 

View solution in original post

6 REPLIES 6
ballardw
Super User

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
Ammonite | Level 13

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
Obsidian | Level 7

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
Super User

@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
Super User Tom
Super User

@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.

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4741 views
  • 1 like
  • 4 in conversation