SAS Macro function return SQL result

Question

I'm newbie in SAS and macro functions. I'd like my code more "DRY", so i'd like to put my useful sql queries into macro functions who return the result.

For example, I've tried this :

%macro getName;
  %local nameq;
  PROC SQL noprint;
    SELECT b.name into :nameq from Country as b where b.ccountry = 100;
  quit;
  &nameq
%mend getNNSS;

%let tst = %getName;
%put &tst;

But I get this error :

ERROR 180-322: Statement is not valid or it is used out of proper order.

Could you help me? Thanks

Joe Joe · Accepted Answer · 2015-09-25T14:57:25

The problem here is that you are not actually writing a macro function (which is not a technical feature of the language, in any event, but is a common usage of the term). A SAS macro - of any sort - simply puts text into the input stream, as if it had been typed by the user. Things that start with % will operate behind the scenes, but anything else will simply be placed in the input stream as-is.

A macro function is defined by common usage to be a macro that performs all of its calculations/etc. in such a way that nothing is actually returned to the system except a single value - that way, it could be used in a similar fashion to a function in other languages (or, in fact, in SAS).

As such, this is a macro function:

%macro mymacrofunction;
  5
%mend mymacrofunction;

And this is not:

%macro notafunction;
 data test;
  x=5;
 run;
%mend notafunction;

Because you couldn't say

%let x=%notafunction;

since that would return

%let x=data test; x=5; run;

which would probably error (unless you ran it inside a data step, in which case it would simply not work as desired, presumably).

In the case of your macro:

%macro getName;
  %local nameq;
  PROC SQL noprint;
    SELECT b.name into :nameq from Country as b where b.ccountry = 100;
  quit;
  &nameq
%mend getNNSS;

What that will do when used with a let statement:

%let tst=proc sql noprint; select ... ; quit; &nameq

That's not what you want, is it.

You have several ways to work around this.

PROC FCMP PROC FCMP is the SAS function-writing language. PROC FCMP does write real functions, more or less - the 'less' is that they're not actually more efficient than the code they contain, contrary to other languages, but they do otherwise work like C-type functions (they operate entirely behind the scenes and perform their own calculations on their own local variables, returning a single value, or with a call routine returning multiple values). So you could replace your macro function with a FCMP function, assuming you are running 9.3 or newer.
DOSUBL DOSUBL is a way of asking SAS to run a macro entirely behind the scenes, which will allow you to do more or less what you're trying to do.
RUN_MACRO in FCMP This would work almost directly with what you're doing: you just wrap your macro in a FCMP function using RUN_MACRO to call it. This is probably your best bet in terms of simplicity.

For more reading on macro functions, sascommunity.org has a page on the subject which links to a lot of papers; or look on a search engine or LexJansen.com for papers on writing Macro Functions.

SAS Macro function return SQL result

1 Answers