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
Maxim 1: Read the Documentation.
From EXIST Function:
Required Argument
member-name
is a character constant, variable, or expression that specifies the SAS library member. Ifmember-nameis blank or a null string, then EXIST uses the value of the _LAST_ system variable as the member name.
If you created any dataset in your SAS session before you invoked the macro, the EXIST function will check for this dataset (and therefore return true) if the first argument to the function is empty. Add a check:
%if %sysfunc(exist(&prevwkfile)) and "&prevwkfile" ne "" %then
Maxim 1: Read the Documentation.
From EXIST Function:
Required Argument
member-name
is a character constant, variable, or expression that specifies the SAS library member. Ifmember-nameis blank or a null string, then EXIST uses the value of the _LAST_ system variable as the member name.
If you created any dataset in your SAS session before you invoked the macro, the EXIST function will check for this dataset (and therefore return true) if the first argument to the function is empty. Add a check:
%if %sysfunc(exist(&prevwkfile)) and "&prevwkfile" ne "" %then
PS you can also check the macro variable &SQLOBS.
Hello Sir,
It worked!
Thank you so very much for taking the time to read my post and pen an easy to understand response and 2 solutions to the problem!
Sincerest apologies for not thinking afar enough to check the documentation.
Best Regards
Since you are getting the name from the metadata there is no need to check if the dataset exists.
You just need to check whether your query found any names, which you can so by checking &SQLOBS.
If you want to check for dataset existence then perhaps it the the other one you should check?
%macro check_exists;
%local prevwkfile;
/* this looks for the latest file from any previous runs */
proc sql noprint;
select cats(libname,'.',nliteral(memname))
into :prevwkfile
from dictionary.tables
where libname = 'MYLIB'
and Memname Like 'PREV_WK_LIST_%'
having MODATE=MAX(MODATE)
;
%if &sqlobs %then %do;
%put "The previous week's file is &prevwkfile";
%if %sysfunc(exist(mylib.mydata_&runDate_&runDate.)) %then %do;
create table provLastWk as
select distinct prov_id
from mylib.mydata_&runDate_&runDate.
where prov_id in (select prov_id from &prevwkfile)
;
%end;
%end;
quit;
%mend check_exists;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.