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
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 ...
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.
Is there a reason you want to delete macro variables set by Proc SQL?
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.
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.
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 ...
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.