CODEWITHSUNDEEP
sas
SMW
SMW

Reputation: 502

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

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;

Upvotes: 0

Views: 497

Answers (2)

Tom
Tom

Reputation: 51566

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);

Upvotes: 0

Joe
Joe

Reputation: 63424

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

Upvotes: 3

Related Questions