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
/***************************************************************
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?
Do you get any messages? What happens if you turn on MLOGIC option? SYMBOLGEN option?
Possible causes.
1) Your macro variable does not exist.
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
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
/***************************************************************
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?
Thank you very much for catching my "stupid user trick".
Added semicolon and everything started working. Thank you again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.