function style macro in SAS with PROC SQL?

Question

Is it possible to make a macro of this form work?

    %macro tableMath(input1,input2);
    %local result;
    proc sql; ---some code here using inputs--- quit;
    proc sql; ---more code here--- quit;
    proc sql;
    select something into: result
    quit;
    &result
    %mend;

I want to run some fairly complicated logic on each observation of a dataset, and in any other language I've used before the way to do this would be to encapsulate it in a function that returns a result each time it's called--I'm not sure how to do this logic in SAS however.

EDIT: input1 and input2 would be columns of a dataset and result would be used to create a new column in some other macro in another part of the program. I don't need a specific code solution I just literally don't get how you're supposed to do traditional function logic where you need a return value in SAS...

stackoverflow.com/questions/33817518/… In this question it's brought up that you can't use anything without a % or else it will pollute the processing stream and you won't get the right return value. Does this mean you literally can't use PROC SQL or a DATA step in macros where you need a return value?? — S420L
There are ways to return a value from a macro . There are also ways to write “function-style macros” but they aren’t true functions. The macro language is a pre-processor, so if you have PROC SQL code that calls a macro, the macro will execute before the SQL code executes. If you can make a little example of how you hope to use your function, that would make this question more specific/answerable. — Quentin
You can try using a data step to code your complicated logic, which would return a result for every row it iterates. After that, use a call execute() to run some step, if the result matches your wished criteria. As Quentin said, there are ways... But you'd have to be a bit more specific for us. — samkart
I want to run some fairly complicated logic on each observation of a dataset If you're newer to SAS I'd say explain that logic. Usually it's not as complex as it seems or there are better methods to use in SAS, even if it's a few steps. — Reeza

Quentin Quentin · Accepted Answer · 2018-07-14T11:37:01

As Richard wrote, function-style macros emit SAS code. The general rule of developing function-style macros is that they contain only macro language statements. Any SAS code they contain will be emitted. Historically, this made it difficult/annoying to write a function-style macro that would process data like you would with a DATA step. Luckily, SAS has added a function, DOSUBL, which makes it easier to write function-style macros that execute SAS code in a "side session" and emit a result. See Rick Langston's paper.

Here is an example of a function-style macro which used DOSUBL to count the number of records in a table, and emits the count. (This is a very inefficient way to get a record count, just an example of doing something in SQL).

%macro SQLcount(table);
  %local rc emit; 

  %let rc=%sysfunc(dosubl(%nrstr(
     proc sql noprint;
      select count(*) into :emit trimmed
      from &table
     quit;
  )));

  &emit 
%mend ;

It can be used like:

proc sql ;
  select name
        ,%SQLcount(sashelp.shoes) as ShoeCount  /*emits 395*/
  from sashelp.class
  ;
quit ;

When the above step runs, it will return 19 rows of names from sashelp.class, and the value of ShoeCount will be 395 on every row. Note that the macro SQLcount only executed once. While the PROC SQL step is being compiled/interpreted the call to SQLcount is seen and the macro is executed and emits 395. The step becomes:

proc sql ;
  select name
        ,395 as ShoeCount  /*emits 395*/
  from sashelp.class
  ;
quit ;

DOSUBL uses a "side session" to execute code, which allows you to execute a PROC SQL step in the side session while the main session is interpreting a PROC SQL step.

I can't tell from your question if that sort of use case is what you want. It's possible you want a function-style macro where you could pass values to it from a table, and have the macro execute on each value and return something. Suppose you had a table which was a list of table names, and wanted to use SQL to get the count of records in each table:

data mytables ;
  input table $20. ;
  cards ;
sashelp.shoes
sashelp.class
sashelp.prdsale
;
quit ;

You can do that by using the resolve() function to build macro calls from data, delaying the execution of the macro until the SELECT statement executes:

proc sql ;
  select table
        ,resolve('%SQLcount('||table||')') as count
  from mytables
  ;
quit ;

With that, SQLcount will be called three times, and will return the number of records in each dataset.

table                 count
---------------------------
sashelp.shoes         395
sashelp.class         19
sashelp.prdsale       1440

The macro call is not seen when the PROC SQL step is interpreted, because it is hidden by the single quotes. The resolve function then calls the macro when the SELECT statement executes, passing the value of table as a parameter value, and the macro emits the record count. This is similar to a CALL EXECUTE approach for using data to drive macro calls.

function style macro in SAS with PROC SQL?

4 Answers