SAS - How to return a value from a SAS macro?

Question

I would like to return a value from a SAS macro I created but I'm not sure how. The macro computes the number of observations in a dataset. I want the number of observations to be returned.

%macro nobs(library_name, table_name);
  proc sql noprint;
    select nlobs into :nobs
    from dictionary.tables
    where libname = UPCASE(&library_name)
      and memname = UPCASE(&table_name);
  quit;

  *return nobs macro variable;
  &nobs
%mend;

%let num_of_observations = %nobs('work', 'patients');

Also, I would like the &nobs macro variable that is used within the macro to be local to that macro and not global. How can I do that?

Answer 1

I'll answer the core question Bambi asked in comments:

My main concern here is how to return a value from a macro.

I'm going to quibble with Dirk here in an important way. He says:

A SAS macro inserts code. It can never return a value, though in some cases you can mimic functions

I disagree. A SAS macro returns text that is inserted into the processing stream. Returns is absolutely an appropriate term for that. And when the text happens to be a single numeric, then it's fine to say that it returns a value.

However, the macro can only return a single value if it only has macro statements in addition to that value. Meaning, every line has to start with a %. Anything that doesn't start with % is going to be returned (and some things that do start with % might also be returned).

So the important question is, How do I return only a value from a macro.

---

In some cases, like this one, it's entirely possible with only macro code. In fact, in many cases this is technically possible - although in many cases it's more work than you should do.

Jack Hamilton's linked paper includes an example that's appropriate here. He dismisses this example, but that's largely because his paper is about counting observations in cases where NOBS is wrong - either with a WHERE clause, or in certain other cases where datasets have been modified without the NOBS metadata being updated.

In your case, you seem perfectly happy to trust NOBS - so this example will do.

A macro that returns a value must have exactly one statement that either is not a macro syntax statement, or is a macro syntax statement that returns a value into the processing stream. %sysfunc is an example of a statement that does so. Things like %let, %put, %if, etc. are syntax statements that don't return anything (by themselves); so you can have as many of those as you want.

You also have to have one statement that puts a value in the processing stream: otherwise you won't get anything out of your macro at all.

Here is a stripped down version of Jack's macro at the end of page 3, simplified to remove the nlobsf that he is showing is wrong:

 %macro check;

   %let dsid = %sysfunc(open(sashelp.class, IS));
   %if &DSID = 0 %then
   %put %sysfunc(sysmsg());

   %let nlobs = %sysfunc(attrn(&dsid, NLOBS));

   %put &nlobs;

   %let rc = %sysfunc(close(&dsid));

 %mend;

That macro is not a function style macro. It doesn't return anything to the processing stream! It's useful for looking at the log, but not useful for giving you a value you can program with. However, it's a good start for a function style macro, because what you really want is that &nlobs, right?

 %macro check;

   %let dsid = %sysfunc(open(sashelp.class, IS));
   %if &DSID = 0 %then
   %put %sysfunc(sysmsg());

   %let nlobs = %sysfunc(attrn(&dsid, NLOBS));

   &nlobs

   %let rc = %sysfunc(close(&dsid));

 %mend;

Now this is a function style macro: it has one statement that is not a macro syntax statement, &nlobs. on a plain line all by itself.

It's actually more than you need by one statement; remember how I said that %sysfunc returns a value to the processing stream? You could remove the %let part of that statement, leaving you with

 %sysfunc(attrn(&dsid, NLOBS))

And then the value will be placed directly in the processing stream itself - allowing you to use it directly. Of course, it isn't as easy to debug if something goes wrong, but I'm sure you can work around that if you need to. Also note the absence of a semi-colon at the end of the statement - this is because semicolons aren't required for macro functions to execute, and we don't want to return any extraneous semicolons.

Let's be well behaved and add a few %locals to get this nice and safe, and make the name of the dataset a parameter, because nature abhors a macro without parameters:

 %macro check(dsetname=);

   %local dsid nlobs rc;

   %let dsid = %sysfunc(open(&dsetname., IS));
   %if &DSID = 0 %then
   %put %sysfunc(sysmsg());

   %let nlobs = %sysfunc(attrn(&dsid, NLOBS));

   &nlobs

   %let rc = %sysfunc(close(&dsid));

 %mend;

 %let classobs= %check(dsetname=sashelp.class);

 %put &=classobs;

There you have it: a function style macro that uses the nlobs function to find out how many rows are in any particular dataset.

Answer 2

A SAS macro inserts code. It can never return a value, though in some cases you can mimic functions, usually you need a work around like

%nobs(work, patients, toReturn=num_of_observations )

** To help you understand what happens, I advice printing the code inserted by the macro in your log: ;

options mprint;

We pass the name of the macro variable to fill in to the macro, I find it most practical to

• not require the user of my macro to put quotes around the libary and member names

• make the name of the variable a named macro variable, so we can give it a default;

  %macro nobs(library_name, table_name, toReturn=nobs);

Make sure the variable to return exists

• If it exists it is known outside of this macro.

• Otherwisse if we create it here, it wil by default be local and lost when we leave the macro;

  %if not %symexist(&toReturn.) %then %global &toReturn.;
  

In the SQL, I

• use the SASHELP.VTABLE, a view provided by SAS on its meta data
• add the quotes I omitted in the macro call ("", not '': macro variables are not substituted in single qoutes)

• use the macro %upcase function instead of the SAS upcase function, as it sometimes improves performance;

  proc sql noprint;
      select nobs
      into :&toReturn.
      from sashelp.vtable
      where libname = %UPCASE("&library_name.")
      and memname = %UPCASE("&table_name.");
  quit;
  

  %mend;

Pay attention if you call a macro within a macro, Run this code and read the log to understand why;

%macro test_nobs();
    %nobs(sashelp, class); ** will return the results in nobs **;

    %nobs(sashelp, shoes, toReturn=num_of_shoes);

    %let num_of_cars = ;
    %nobs(sashelp, cars, toReturn=num_of_cars);

    %put NOTE: inside test_nobs, the following macro variables are known;
    %put _user_;
%mend;

%test_nobs;
%put NOTE: outside test_nobs, the following macro variables are known;
%put _user_;