How to create a macro variable within a macro?

Question

I am wondering how to create a SAS macro variable within a block of SAS %MACRO statement? It appears that my CALL SYMPUT or my SELECT INTO statements aren't working when they are in a block of %MACRO statement.

%MACRO NONDATE_FORMAT_CHECK(varname=,output=);
    PROC SQL;
    CONNECT TO NETEZZA AS NET 
    (SERVER=&server 
    DATABASE=&database
    USER=&NBKID 
    PASSWORD=&NBKPASSWD);
    CREATE TABLE WORK.DT_FMT&output AS SELECT *
    FROM CONNECTION TO NET
        (SELECT 'FORMAT_IS_DATE' AS DT_FMT_INDICATOR
        FROM &input_database&input_table
        WHERE (SELECT COUNT(*)
        FROM &input_database&input_table
        WHERE SUBSTR(&varname,1,10) LIKE '____-__-__') > 0
        LIMIT 1);
    DISCONNECT FROM NET;
    QUIT;

    PROC SQL;
        SELECT DT_FMT_INDICATOR INTO :DT_FMT_CHECK_&varname
        FROM WORK.DT_FMT&output;
    QUIT;
%MEND NONDATE_FORMAT_CHECK;

Thanks in advance.

Answer 1

Scope is your issue here. By default, the CALL SYMPUT and SELECT INTO create a macro variable in the local symbol table, (in the case of CALL SYMPUT, if it is nonempty, but in your macro it is).

See How Macro Variables are Assigned and Resolved for more information.

To fix this, your best bet is a %GLOBAL statement in your macro prior to the assignment in PROC SQL or CALL SYMPUT, or use CALL SYMPUTX which lets you specify the scope.

Answer 2

You are creating the macro variable, but your macro is never using it before it exits.

Did you intend for the macro variable to be available after the macro ends? If so you need to either create it first

%let DT_FMT_CHECK_george= ;
%NONDATE_FORMAT_CHECK(varname=george,output=fred);

or make it GLOBAL before assigning a value to it.

%if not %symexist(DT_FMT_CHECK_&varname) %then %global DT_FMT_CHECK_&varname;
PROC SQL;
    SELECT DT_FMT_INDICATOR INTO :DT_FMT_CHECK_&varname
    FROM WORK.DT_FMT&output;
QUIT;