I have the following code for checking if a file from the previous week's run has been created. If the file exists, then we have to check it for excluding providers form the previous week's run. If the file is not present, then we create it. So for the very first run of the code, when there was no file created previously, the exist function should evaluate to False. But my code is evaluating it to true. I do not understand what I am doing wrong. I fixed the issue with @Kurt_Bremser solution where he showed how to use the find() function to set a flag and then check the flag in the macro but I would still like to understand why my code didn't work. Thanks to whoever can explain this, as simplified as possible 🙂 Here's the code and the SAS log entries. %let libname mylib 'my folder path';
%macro check_exists;
%let prevwkfile = ;
/* this looks for the latest file from any previous runs */
proc sql;
select cats(libname,'.',memname) into: prevwkfile
from dictionary.tables
where libname = 'MYLIB'
and Memname Like 'PREV_WK_LIST_%'
having MODATE=MAX(MODATE);
quit;
%put "The previous week's file is &prevwkfile";
/* if a previous week file exists */
%if (%sysfunc(exist(&prevwkfile))) %then
%do;
proc sql;
create table provLastWk as
select distinct prov_id
from mylib.mydata_&runDate_&runDate.
where prov_id in (select prov_id from &prevwkfile);
quit;
%end;
%mend check_exists;
SAS Log entries
1879 %check_exists;
MLOGIC(CHECK_EXISTS): Beginning execution.
MLOGIC(CHECK_EXISTS): %LET (variable name is PREVWKFILE)
MPRINT(CHECK_EXISTS): PROC SQL;
MPRINT(CHECK_EXISTS): SELECT CATS(libname,'.',memname) INTO: prevwkfile FROM dictionary.tables
WHERE libname = 'MYLIB' AND Memname Like 'PREV_WK_LIST_%' HAVING MODATE=MAX(MODATE);
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: No rows were selected.
MPRINT(CHECK_EXISTS): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MLOGIC(CHECK_EXISTS): %PUT "The previous week's file is &prevwkfile"
SYMBOLGEN: Macro variable PREVWKFILE resolves to
"The previous week's file is "
SYMBOLGEN: Macro variable PREVWKFILE resolves to
MLOGIC(CHECK_EXISTS): %IF condition (%sysfunc(exist(&prevwkfile))) is TRUE -- why is this evaluating to true when I don't have any files that was generated the previous week?
MPRINT(CHECK_EXISTS): proc sql;
SYMBOLGEN: Macro variable RUNDATE resolves to 20230815
SYMBOLGEN: Macro variable PREVWKFILE resolves to
NOTE 137-205: Line generated by the invoked macro "CHECK_EXISTS".
1 proc sql; create table provLastWk as select distinct prov_id
1 ! from mylib.mydata_&runDate. where prov_id in (select prov_id
1 ! from &prevwkfile); quit; proc sql;
-
22 -- I understand why this sql error happened.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.
MPRINT(CHECK_EXISTS): create table provLastWk as select distinct prov_id from
mylib.mydata_20230815 where prov_id in (select prov_id from );
MPRINT(CHECK_EXISTS): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
... View more