Sas macro parameter

[AJ_Brien]

Hello,

I have a SAS macro that takes in a parameter in this format:

%macro TEST(DBName.TableName);

It is preferred to have just a single argument so I'm not looking to pass on DBName and TableName as 2 separate parameters.

I'm facing the following concerns with this approach:

1. Is this the right syntax to define a macro with such parameter?

2. How do I reference this parameter within the macro? I'm doing a basic sql query within the macro to access it:

proc sql noprint;
select top 500 * from &DBName.TableName.;
quit;

Log gives me these errors, as expected:

ERROR: Symbolic variable name DBNAME.TABLENAME must contain only letters, digits, and underscores.
ERROR: Invalid macro parameter name DBNAME.TABLENAME. It should be a valid SAS identifier no longer than 32 characters.
ERROR: A dummy macro will be compiled.

Appreciate any help!

[Rick_SAS]

The macro should have a variable that you access (by using an ampersand) inside the macro, like this:

%macro TEST(Table=);
   %put &Table;     /* name of the macro variable is Table. The value is &Table */
%mend;

%TEST(table=DBName.TableName);

If that doesn't answer your question, please show the signature of the %TEST macro.

[AJ_Brien]

So the main purpose of doing this is to let other folks using this macro is to let them know what input parameters they are expected to send when they use this macro in their codes. If I name the macro variable as 'table', they wouldn't know that they need to send the complete name with the database name, hence something like dbname.tablename needs to be mentioned in the arguments being passed so that they know the exact format.

[Rick_SAS · Accepted solution]

OK, then use

%macro TEST(DBname_TableName=);
...
%mend;

The error you're getting is because periods ("dot") are not permitted in SAS variable names. Also, the period is used to terminate a macro reference, so in the statement

   select top 500 * from &DBName.TableName.;

the first period ends the macro and SAS is looking for the macro variable DBName so that it can figure out how to resolve &DBName. and then append the string TableName to it.

[AJ_Brien]

That makes sense, I think I can do that. For some reason I was under the impression that the dot in the actual parameter needs to reflect in the parameter call as well. This was helpful, thank you!

[Tom]

One of the nice features of how SAS macros work is the ability to call the macro using the parameter name, even for parameters that the macro also allows you call by position.

So if you defined a macro using a positional parameter like this:

%macro myreport(table);
 .... &table. ....
%mend myreport;

Users can then call it in two ways. 

%myreport(mylibref.mymembername)
%myreport(table=mylibref.mymembername)

If you use the second style in your code that calls the macro then readers of your code have a better idea of  the meaning of the value being passed since they can see both the value the calling code is passing and the name that the macro has given for the parameter.