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