%sysfunc(exist) is evaluating true when dataset is not present

[DPurush]

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

[Kurt_Bremser · Accepted solution]

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

[Kurt_Bremser]

PS you can also check the macro variable &SQLOBS.

[DPurush]

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

[Tom]

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;

[DPurush]

Thanks Tom ! 🙂