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