topic Re: Error when deleting global macro variables in SAS Enterprise Guide

Error when deleting global macro variables

madara155 — Wed, 03 Aug 2022 05:02:25 GMT

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

Re: Error when deleting global macro variables

Kurt_Bremser — Wed, 03 Aug 2022 06:03:50 GMT

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.

Re: Error when deleting global macro variables

PeterClemmensen — Wed, 03 Aug 2022 06:35:20 GMT

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

Re: Error when deleting global macro variables

ballardw — Wed, 03 Aug 2022 10:10:31 GMT

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.

Re: Error when deleting global macro variables

madara155 — Wed, 03 Aug 2022 11:32:25 GMT

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.

Re: Error when deleting global macro variables

FreelanceReinh — Wed, 03 Aug 2022 12:07:06 GMT

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

Re: Error when deleting global macro variables

Quentin — Wed, 03 Aug 2022 12:12:30 GMT

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;

Re: Error when deleting global macro variables

Astounding — Wed, 03 Aug 2022 15:06:59 GMT

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.

Re: Error when deleting global macro variables

madara155 — Thu, 04 Aug 2022 22:50:30 GMT

Thanks for your kind support! Appreciate it!