Error when deleting global macro variables

[madara155]

I have been using the following bit of code to delete the Global macro variables.

%macro delvars;
data vars;
set sashelp.vmacro;
run;
data _null_;
set vars;
if scope='GLOBAL' then call execute('%symdel '
||trim(left(name))||';');
run;
%mend;

Recently started getting the following two errors: (rest of the global macro variables seems to get deleted)

ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_ALL.

ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_STMT.

I have no idea what these are. Any thoughts?

Thanks

[Kurt_Bremser]

Maxim 6: Google Is Your Friend.

A search for the names of these variables reveals that they are set by PROC SQL and cannot be deleted.

[PeterClemmensen]

Is there a reason you want to delete macro variables set by Proc SQL?

[ballardw]

One suspects that what you really are attempting to get to are the _USER_ defined variables, i.e. create by you, that are also Global in scope.

If that is the case then please say so.

[madara155]

Hi

Yes, I just want to delete the global macro variables that I have defined. But I could not find another way to delete just the ones I defined.

Thanks.

[FreelanceReinh · Accepted solution]

If your naming convention for macro variables follows the recommendation of item 7 in Creating User-Defined Macro Variable Names -- "The prefixes AF, DMS, SQL, and SYS are not recommended ..." --, you can exclude those prefixes in your IF condition:

if scope='GLOBAL' & name ~in: ('AF' 'DMS' 'SQL' 'SYS') then ...

[Quentin]

Unfortunately, this is a hard task.  Many of the clients (EG, Studio, etc) litter the global symbol table with macro variables, which makes it difficult to know which are "yours".  

That said, there is a tech support note which says that macro variables starting with SYS and AF are reserved name spaces, so I delete everything but those:

data _null_;
  set sashelp.vmacro;
  where scope='GLOBAL' and offset=0 and name not like "SYS%" and name not like "AF%";  
  call execute('%nrstr(%%)symdel '||trim(left(name))||';');
run;

For the macro variables you can't delete, it is possible to set them to null:

data _null_;
  set sashelp.vmacro;
  where scope='GLOBAL' and offset=0 and (name like "SYS%" or name like "AF%");  
  call execute('%nrstr(%%)let '||trim(left(name))||'=;');
run;

[Astounding]

Here's one more change I would recommend.

if scope='GLOBAL' and offset=0 and .....

Macro variables within sashelp.vmacro do not necessarily appear in just one observation.  They get stored in 200-character chunks.  So you may be trying to delete long macro variables twice.  Or more than twice.  Adding OFFSET=0 as a condition selects just one occurrence of the macro variable within sashelp.vmacro, and only attempts to delete the macro variable once.

[madara155]

Thanks for your kind support! Appreciate it!