BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
DPurush
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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
DPurush
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6
Thanks Tom ! 🙂

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 658 views
  • 3 likes
  • 3 in conversation