Macro Variable %Symexist

[sameer112217]

data sample;
set sashelp.class;
run;

data sample1;
set sashelp.class;
run;

proc sql;
select  memname into :deletelist seperated by ' '
from dictionary.tables where
libname = "WORK";
quit;

%global deletelist;
%put &deletelist;

%symdel deletelist;

%Macro deletetables;
%if %symexist (deletelist) %then %do;
Proc datasets library= work nolist;
delete &deletelist;
quit;
%end;
%else %do;
%put The macro variable deletelist does not exsit;
%end;

%mend deletetables;

%deletetables;

In the above examples I am keeping this piece of program in my job flow. This is just an example to what the logic I need for my project. When macro variable deletelist is created it has two values sample sample1 when you run first time but when it is deleted and the next time you run the same program I get the message with an error. "WARNING: Apparent symbolic reference DELETELIST not resolved" because there is no values in the macro variable now and it is already deleted when the program ran first time, how can I resolve this with other method/logic to avoid error? Does %symexist function check the existence of macro variable? I tried %sysfunc(exist but it doesn't work in case of macro variable like this.

[Astounding · Accepted solution]

If you want to check whether a macro variable exists, you can examine dictionary.macros.  But there is an approach that is both easier and better.

You already guarantee that the variable does exist by issuing this statement:

%global deletelist;

So leave it there.  Don't get rid of it by issuing the %SYMDEL command.  Instead, check to see whether it contains anything.  (After all, if the macro variable exists, but has a null value, you should skip PROC DATASETS.)

In practice, this means changing this logic:

%if %symexist (deletelist) %then %do;
Proc datasets library= work nolist;
delete &deletelist;
quit;
%end;

The replacement logic:

%if %length(&deletelist) %then %do;
Proc datasets library= work nolist;
delete &deletelist;
quit;

%let deletelist=;
%end;

%ELSE is no longer needed, unless you just want to write a message:

%else %put No data sets were deleted.;

[Tom]

Your logic is over complicated as @Astounding has already answered.

But the reason the macro variable was not created is because of how PROC SQL works. When the result set has no observations then the macro variable is not created.

An easy way to handle this fact is to get in the habit of setting a default value with a %LET statement before the SELECT statement.

proc sql noprint ;
%let deletelist=;
select nliteral(memname)
  into :deletelist seperated by ' '
  from dictionary.tables 
  where libname = "WORK"
;
quit;

You can also test the automatic macro variable SQLOBS after the select statement.

For example you might change how you are using the macro variable so that it contains the whole DELETE statement instead of just the list of names.  Then there is no need for macro logic (or a macro) at the actual delete step.

proc sql noprint ;
%let deletelist=;
select nliteral(memname)
  into :deletelist seperated by ' '
  from dictionary.tables 
  where libname = "WORK"
;
%let deletelist=%sysfunc(ifc(&sqlobs,delete &deletelist,%str( )));
quit;
...
proc datasets lib="WORK" nolist;
&deletelist;
run; quit;

[sameer112217]

Thank you tom and astounding