Macros that need to return a single value, as opposed to generating code are often written as functions so they can be used, for example, as follows:
- %let total = %getMax(data= . . ., var = . . . );
- retain denom %getMax(data= . . ., var = . . . );
- store the value into a macro variable that the calling program must know about;
- save the value into a data set that the calling program must use to get the value;
- use the %sysfunc macro along with the data access functions.
(data = /* name of the dataset being updated */
,key = /* name of surrogate key variable */
%if &&&key = . or &&&key = %then
%do; /* generate a new surrogate key value */
%let rc = %sysfunc(dosubl(
'proc sql noprint;
into:newkey from &data;quit;'
%put NOTE: &key value of &newkey generated.;
%end; /* generate a new surrogate key value */
The macro is called unconditionally by the update process and if the macro variable, from the Data Entry page (a SAS Server Page), for the key is missing, a new row is being added and this requires a new key value. The DOSUBL function is used, via %SYSFUNC) to run the PROC SQL code to get the next auto-number key value. Since DOSUBL is used, the macro execution does not return the SQL code to the current SAS session's input stack. Executing the macro returns:
- the new key value, &newkey, for a new row.
- the current key value, &&&key, for an existing row.