Name/Title of the macro as a variable inside a macro SAS

Question

I am attempting to use a PARMBUFF macro in order to repeat a data step with 20 different client definitions (one example below).

DATA _NULL_;
%GLOBAL bank1;
%LET bank1 = O.OWNER LIKE 'XXXXX%';
RUN;

The data step will create separate tables defined by the client macro variables that are created above.

%MACRO CLIENTBUILD/PARMBUFF; 
%LET N=%SYSFUNC(COUNTW(&SYSPBUFF,%STR(,)));
%DO I=1 %TO &N;
  %LET CLIENT=%SCAN(%QSYSFUNC(COMPRESS(%BQUOTE(&SYSPBUFF),%STR(%(%)))),&I,%STR(,));
CREATE TABLE CLIENT AS
SELECT DISTINCT C.DATE,
            C.TIME,
            C.RELEASE,
            C.TASK
FROM CALLS C
INNER JOIN OWN_GRNT O ON SUBSTR(C.TASK,1,9)= O.TASK 
AND    &CLIENT
;
%END;
%MEND;

I would like to create this table with the NAME (String) of the macro…but I am having problems referencing the string of the macro for the name of the table. I attempted to reformat the macro as follows (in order to try and call out the string/name of the macro for the name of the table) by using

%MACRO CLIENTBUILD/PARMBUFF; 
%LET N=%SYSFUNC(COUNTW(&SYSPBUFF,%STR(,)));
%DO I=1 %TO &N;
  %LET CLIENT=%SCAN(%QSYSFUNC(COMPRESS(%BQUOTE(&SYSPBUFF),%STR(%(%)))),&I,%STR(,));
CREATE TABLE '&CLIENT' AS
SELECT DISTINCT C.DATE,
                C.TIME,
                C.RELEASE,
                C.TASK
FROM CALLS C
INNER JOIN OWN_GRNT O ON SUBSTR(C.TASK,1,9)= O.TASK 
AND    &CLIENT
;
%END;
%MEND;
Then calling the macro out with – 
PROC SQL;
%CLIENTBUILD(&NCT);
QUIT;

But this results in the error :

ERROR: The value &CLIENT is not a valid SAS name.`

Is there a solution to call out the macro title that is dependent on the macro variable being used to define the client? Once resolved, I would like to just create every client data set with ....

Proc SQL; 

%CLIENTBUILD(&bank1,&bank2,&bank3,..........);

QUIT;

Remove the single quotes around &CLIENT -- as in `CREATE TABLE &client AS...` The single quotes are masking the &CLIENT variable from being resolved. SAS Tables cannot start with `&`, and that is giving you the error. — DomPazz, Jun 18 '15 at 15:50
The problem with that is it will resolve the actual macro definition (O.OWNER LIKE 'XXXXX%') instead of just the name - "bank1"...I only want it to resolve at the bottom in the "WHERE &CLIENT" section (error in original post...written as AND &CLIENT instead), and use the string (bank1) as the name of the table... — SMW, Jun 18 '15 at 15:57
Use macro quoting (`%nrstr` for example) to control resolution. And if you want `bank1` not `&bank1` you will have some difficulty, because that's not how SAS macro variables work. You'll need to re-think the approach here - in particular, it sounds like you should pass two parameters, not one. — Joe, Jun 18 '15 at 16:09
Entertained that thought, but had problems constructing the PARMBUFF with two parameters...attempted to include an additional "%LET VAR2=%SCAN(%QSYSFUNC(COMPRESS(%BQUOTE(&SYSPBUFF),%STR(%(%)))),&I,%STR(,));" in the macro build... then execute as %CLIENTBUILD(&BANK1,&VAR2); but had no success. — SMW, Jun 18 '15 at 16:24

score 3 · Accepted Answer · edited May 23 '17 at 11:51

First, a suggestion. Don't build your application this way. This is a very messy and risky method to do this. You're using global macro variables that should be parameters, and you're trying to text parse a list of those macro variables' names.

Write the macro this way:

%macro maketable(table=,where=);
  proc sql;
    create table &table. as 
      ...
      where &where.;
  quit;
%mend maketable;

And then construct calls to %maketable from the dataset that stores your WHERE clauses (I assume that's not just open code? If it is, put it in a dataset via excel or something.)

proc sql;
  select cats('%maketable(table=',tablename,',where=%nrstr(',whereclause,'))'
     into :tablecall separated by ' '
     from my_whereclause;
quit;

And then just include &tablecall. on the next line and when you run it, you'll get all of your tables.

(As an aside, the odds are you probably can even not make 20 tables, but make 1 table with 20 values of a by variable - but you don't explain what you're doing after this, so it's hard to tell.)

If you must do it this way, you need to understand how macro variable resolution works. See my answer here for some explanation, or my SGF paper on the subject. SAS documentation also is pretty good at explaining this.

Basically, you need to single resolve your table name &table to define the table name, and then multiple resolve it to get the where clause.

Here's an example using sashelp.class.

%let abc= age gt 13;
%let def= age lt 13;
%let ghi= age eq 13;
%macro doit/parmbuff;
    %LET N=%SYSFUNC(COUNTW(&SYSPBUFF,%STR(,)));
    %DO I=1 %TO &N;
      %LET CLIENT=%SCAN(%QSYSFUNC(COMPRESS(%BQUOTE(&SYSPBUFF),%STR(%(%)))),&I,%STR(,));
      %PUT &=CLIENT;

      proc sql;
        create table &client. as 
            select * from sashelp.class
            where &&&client.;
      quit;
    %end;
%mend doit;

%doit(abc,def,ghi);

&&&client resolves &client to abc, then leaves an ampersand there (so &abc) - and then resolves that, so it resolves &abc to its value (the where clause).

Gave you a check ...at the end of it all, stepping back and rebuilding (the program and macro structure) was the solution. And as for your suggestion of creating an additional data set holding all the variables...opened up some flexibility. Thank you much! — SMW, Jun 18 '15 at 22:53

score 0 · Answer 2 · answered Jun 19 '15 at 19:10

You do not need to use PARMBUFF for this type of application. Just don't use comma as your delimiter. For example you could use spaces or | as in the example below.

%macro doall(list);
  %local i item ;
  %do i=1 %to %sysfunc(countw(&list,|));
    %let item=%scan(&list,&i,|);
    ... process current item ...
  %end;
%mend doall ;
%doall(A|B|C|D);

Name/Title of the macro as a variable inside a macro SAS

2 Answers2