topic Re: Macro Variable %Symexist in SAS Programming

Macro Variable %Symexist

sameer112217 — Sat, 25 Aug 2018 12:59:47 GMT

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.

Re: Macro Variable %Symexist

Astounding — Sat, 25 Aug 2018 14:18:44 GMT

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.;

Re: Macro Variable %Symexist

Tom — Sat, 25 Aug 2018 17:23:48 GMT

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;

Re: Macro Variable %Symexist

sameer112217 — Sat, 25 Aug 2018 18:53:51 GMT

Thank you tom and astounding