topic Re: Using %LET variable for a Macro Call in New SAS User

Using %LET variable for a Macro Call

jsaspelin — Mon, 25 Mar 2019 22:22:25 GMT

I'm not sure that my subject correctly explains what I am trying to do, but here is my question.

I have a %LET variable (0 or 1) that I want to set at the beginning of my SAS script. I want to use this value to tell my script whether or not to execute one of two PROC SQL statements. Everything seems to work correctly with my MACRO call if I "hard code" the value into the call, but nothing executes if I attempt to use the %LET variable I am setting at the beginning of my script.

I have tried a number of ways without success. What am I doing wrong? Any help would be greatly appreciated.

Here is my code:

/***************************************************************
   SET variable at the beginning of the SAS script.

   Will be set to either 0 or 1 depending on circumstances.
***************************************************************/
%LET Upload_Has_Invalid_TIN = 0

/***************************************************************
Want to use the above variable to execute determine if all
or only a portion of the PROC SQL script is executed.
***************************************************************/
%MACRO RunFull(HasTIN);

     %IF &HasTIN = 1 %THEN
          %DO;
               PROC SQL;
                    CREATE TABLE WORK.TMP_TIN_PTY_UNION
                    AS
                         select    PTY1.SortKey
                                   ,PTY1.TIN
                            FROM    WORK.tmp_TIN_PTY PTY1
                    UNION
                         select    PTY2.SortKey
                                   ,PTY2.TIN
                            FROM    WORK.tmp_TIN9 PTY2
               ;
               QUIT;
          %END;
     %ELSE %IF &HasTIN = 0 %THEN
          %DO;
               PROC SQL;
                    CREATE TABLE WORK.TMP_TIN_PTY_UNION
                    AS
                         select    PTY1.SortKey
                                   ,PTY1.TIN
                            FROM    WORK.tmp_TIN_PTY PTY1
               ;
               QUIT;
          %END;

%MEND RunFull;

/*****************************************************************
I have attempted to included EACH of the following below

"individually" as a MACRO call immediately below

%MEND RunFull; (shown above)

The only Macro call executions that work are 3 and 4 (Below).

Can I not pass a %LET variable into a %MACRO execution?
*****************************************************************/
/* 1 */
%RunFull(HasTIN=&Upload_Has_Invalid_TIN); /* THIS DOES NOT WORK */

/* 2 */
%RunFull(&Upload_Has_Invalid_TIN); /* THIS DOES NOT WORK */

/* 3 */
%RunFull(0); /* HARDCODING this value WORKS. Defeats purpose of %LET variable*/

/* 4 */
%RunFull(1); /* HARDCODING this value WORKS. Defeats purpose of %LET variable */

Thank you.

John

Re: Using %LET variable for a Macro Call

Tom — Mon, 25 Mar 2019 22:30:13 GMT

Do you get any messages? What happens if you turn on MLOGIC option? SYMBOLGEN option?

Possible causes.

1) Your macro variable does not exist.

Perhaps you set it as a LOCAL macro variable in some earlier macro call that has finished.
Or you ran %SYMDEL and removed it.
The code to set it never ran (or ran in a previous SAS session?)

2) Your macro variable has characters in it other than just digits.

3) Your macro variable has macro quoting that is confusing the implicit %EVAL() of the %IF statement

Re: Using %LET variable for a Macro Call

Reeza — Mon, 25 Mar 2019 22:37:57 GMT

This is the one that should work.

%RunFull(&Upload_Has_Invalid_TIN);

Try restarting SAS, setting the debugging options below and then run your code with the full query and the call above. If it still doesn't work, please post the full log. Just a note, if you're on SAS 9.4TS1M5+ you don't need a macro to have conditional execution of your code. %IF/%THEN will work in open code now.

options mprint symbolgen mlogic;

@jsaspelin wrote:

I'm not sure that my subject correctly explains what I am trying to do, but here is my question.

I have a %LET variable (0 or 1) that I want to set at the beginning of my SAS script. I want to use this value to tell my script whether or not to execute one of two PROC SQL statements. Everything seems to work correctly with my MACRO call if I "hard code" the value into the call, but nothing executes if I attempt to use the %LET variable I am setting at the beginning of my script.

I have tried a number of ways without success. What am I doing wrong? Any help would be greatly appreciated.

Here is my code:

/***************************************************************
   SET variable at the beginning of the SAS script.

   Will be set to either 0 or 1 depending on circumstances.
***************************************************************/
%LET Upload_Has_Invalid_TIN = 0

/***************************************************************
Want to use the above variable to execute determine if all
or only a portion of the PROC SQL script is executed.
***************************************************************/
%MACRO RunFull(HasTIN);

     %IF &HasTIN = 1 %THEN
          %DO;
               PROC SQL;
                    CREATE TABLE WORK.TMP_TIN_PTY_UNION
                    AS
                         select    PTY1.SortKey
                                   ,PTY1.TIN
                            FROM    WORK.tmp_TIN_PTY PTY1
                    UNION
                         select    PTY2.SortKey
                                   ,PTY2.TIN
                            FROM    WORK.tmp_TIN9 PTY2
               ;
               QUIT;
          %END;
     %ELSE %IF &HasTIN = 0 %THEN
          %DO;
               PROC SQL;
                    CREATE TABLE WORK.TMP_TIN_PTY_UNION
                    AS
                         select    PTY1.SortKey
                                   ,PTY1.TIN
                            FROM    WORK.tmp_TIN_PTY PTY1
               ;
               QUIT;
          %END;

%MEND RunFull;

/*****************************************************************
I have attempted to included EACH of the following below

"individually" as a MACRO call immediately below

%MEND RunFull; (shown above)

The only Macro call executions that work are 3 and 4 (Below).

Can I not pass a %LET variable into a %MACRO execution?
*****************************************************************/
/* 1 */
%RunFull(HasTIN=&Upload_Has_Invalid_TIN); /* THIS DOES NOT WORK */

/* 2 */
%RunFull(&Upload_Has_Invalid_TIN);               /* THIS DOES NOT WORK */

/* 3 */
%RunFull(0);                                                       /* HARDCODING this value WORKS. Defeats purpose of %LET variable*/

/* 4 */
%RunFull(1);                                                     /* HARDCODING this value WORKS. Defeats purpose of %LET variable */

Thank you.

John

Re: Using %LET variable for a Macro Call

ballardw — Mon, 25 Mar 2019 22:43:14 GMT

/***************************************************************
   SET variable at the beginning of the SAS script.

   Will be set to either 0 or 1 depending on circumstances.
***************************************************************/
%LET Upload_Has_Invalid_TIN = 0

There is NO semicolon ending that assignment statement.

so the actual value you are assigning is quite problematical.

1) Save the code.

2) Restart SAS;

3) add

%put &Upload_Has_Invalid_TIN;

before the %macro statement.

Only run the code through the %put statement just added. What do you get for the displayed value?

Re: Using %LET variable for a Macro Call

jsaspelin — Fri, 29 Mar 2019 11:39:37 GMT

Thank you very much for catching my "stupid user trick".

Added semicolon and everything started working. Thank you again.