delete macro variables

[Ronein]

Hello

I want to delete all user defined macro variables.

I also want to delete all data sets in work library.

I get error that automatic macro variable are on attempt to be deleted.

My question--How can I ask to delete only user defined macro variables?

ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_ALL.
NOTE: Invalid argument to function SYMDEL('SYS_SQL_IP_A'[12 of 32 characters shown]) at line 26 column 36.
scope=GLOBAL name=SYS_SQL_IP_ALL offset=0 value=-1 _ERROR_=1 _N_=8
ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_STMT.
NOTE: Invalid argument to function SYMDEL('SYS_SQL_IP_S'[12 of 32 characters shown]) at line 26 column 36.
scope=GLOBAL name=SYS_SQL_IP_STMT offset=0 value=  _ERROR_=1 _N_=9
ERROR: Attempt to delete automatic macro variable SYS_TPT_USED.
NOTE: Invalid argument to function SYMDEL('SYS_TPT_USED'[12 of 32 characters shown]) at line 26 column 36.
scope=GLOBAL name=SYS_TPT_USED offset=0 value=NO _ERROR_=1 _N_=10
ERROR: Attempt to delete automatic macro variable SYSDBMSG.
NOTE: Invalid argument to function SYMDEL('SYSDBMSG    '[12 of 32 characters shown]) at line 26 column 36.
scope=GLOBAL name=SYSDBMSG offset=0 value=  _ERROR_=1 _N_=27
ERROR: Attempt to delete automatic macro variable SYSDBRC.
NOTE: Invalid argument to function SYMDEL('SYSDBRC     '[12 of 32 characters shown]) at line 26 column 36.
scope=GLOBAL name=SYSDBRC offset=0 value=0 _ERROR_=1 _N_=28
ERROR: Attempt to delete automatic macro variable AFDSID.
NOTE: Invalid argument to function SYMDEL('AFDSID      '[12 of 32 characters shown]) at line 26 column 36.
scope=AUTOMATIC name=AFDSID offset=0 value=0 _ERROR_=1 _N_=29
ERROR: Attempt to delete automatic macro variable AFDSNAME.
NOTE: Invalid argument to function SYMDEL('AFDSNAME    '[12 of 32 characters shown]) at line 26 column 36.
scope=AUTOMATIC name=AFDSNAME offset=0 value=  _ERROR_=1 _N_=30
ERROR: Attempt to delete automatic macro variable AFLIB.
NOTE: Invalid argument to function SYMDEL('AFLIB       '[12 of 32 characters shown]) at line 26 column 36.
scope=AUTOMATIC name=AFLIB offset=0 value=  _ERROR_=1 _N_=31
ERROR: Attempt to delete automatic macro variable AFSTR1.
NOTE: Invalid argument to function SYMDEL('AFSTR1      '[12 of 32 characters shown]) at line 26 column 36.
scope=AUTOMATIC name=AFSTR1 offset=0 value=  _ERROR_=1 _N_=32
ERROR: Attempt to delete automatic macro variable AFSTR2.
NOTE: Invalid argument to function SYMDEL('AFSTR2      '[12 of 32 characters shown]) at line 26 column 36.
scope=AUTOMATIC name=AFSTR2 offset=0 value=  _ERROR_=1 _N_=33
2                                                          The SAS System                                 08:10 Monday, July 3, 2023

ERROR: Attempt to delete automatic macro variable FSPBDV.

%let x=5;
%let y=100;
%let branch=876;

proc sql ;
create table macro_Vars as
select *
from dictionary.macros
; 
quit;

data tbl;
input ID age;
cards;
1 45
2 18
;
run;


%macro clearALL;
/*delete macro variables*/
data _null_;
set macro_Vars;
call symdel(name);
run;

/*clear libnames and filenames*/
libname _ALL_ clear;
filename _ALL_ clear;

/*deletes all datasets from work*/
proc datasets lib=work kill noprint;
run;
%mend clearALL;
%clearALL

[PaigeMiller]

Please do NOT (in this thread, and in the future) separate the errors from the code that produces them. Show us the ENTIRE log for the DATA step or PROC that produces the errors; we need to see the log from the start of the DATA step or PROC all the way down to the last ERROR message/WARNING/NOTE for that data step or PROC; do not show us partial logs.

[PaigeMiller]

Please read carefully. I said, very specifically: "Show us the ENTIRE log for the DATA step or PROC that produces the errors"

[Ronein]

1                                                          The SAS System                                13:48 Tuesday, July 4, 2023

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         %macro clearALL;
27         /*delete macro variables*/
28         data _null_;
29         set macro_Vars;
30         call symdel(name);
31         run;
32         /*clear libnames and filenames*/
33         libname _ALL_ clear;
34         filename _ALL_ clear;
35         /*deletes all datasets from work*/
36         proc datasets lib=work kill noprint;
37         run;
38         %mend clearALL;
39         %clearALL

ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_ALL.
NOTE: Invalid argument to function SYMDEL('SYS_SQL_IP_A'[12 of 32 characters shown]) at line 39 column 36.
scope=GLOBAL name=SYS_SQL_IP_ALL offset=0 value=-1 _ERROR_=1 _N_=9
ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_STMT.
NOTE: Invalid argument to function SYMDEL('SYS_SQL_IP_S'[12 of 32 characters shown]) at line 39 column 36.
scope=GLOBAL name=SYS_SQL_IP_STMT offset=0 value=  _ERROR_=1 _N_=10
ERROR: Attempt to delete automatic macro variable SYSDBMSG.
NOTE: Invalid argument to function SYMDEL('SYSDBMSG    '[12 of 32 characters shown]) at line 39 column 36.
scope=GLOBAL name=SYSDBMSG offset=0 value=  _ERROR_=1 _N_=29
ERROR: Attempt to delete automatic macro variable SYSDBRC.
NOTE: Invalid argument to function SYMDEL('SYSDBRC     '[12 of 32 characters shown]) at line 39 column 36.
scope=GLOBAL name=SYSDBRC offset=0 value=0 _ERROR_=1 _N_=30
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 31 observations read from the data set WORK.MACRO_VARS.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds

[PaigeMiller]

What do you think this means?

ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_ALL

It means you can't delete this macro variable because it is a SYSTEM generated macro variable. So don't try to delete it. How can you tell which variables are system generated? You look at the data set you created called macro_vars, and you look at the errors generated; that should give you a clue.

[Ronein]

How can I tell SAS-

step 1-Recognize the macro vars that are user defined (It means that  user created them and they were not created automatically)

step 2-Delete the macro vars from step 1

[PaigeMiller]

---

@Ronein wrote:

How can I tell SAS-

step 1-Recognize the macro vars that are user defined (It means that  user created them and they were not created automatically)

step 2-Delete the macro vars from step 1

---

Did you actually look at this MACRO_VARS data set with your own eyes? Did you try to detect a pattern or something in the data step that indicates which macro variables are ones you created and which are created by SAS?

Some things you can figure out yourself.

[Quentin]

My understanding is both macro variable names that start with SYS and those that start with AF cannot be deleted.  Whether the macro variable was system-created or user-created is irrelevant.  A SAS developer decided that %SYMDEL can't delete those variables.  

My practice is to delete global macro variables that don't start with SYS or AF, and for macro variables that do start with SYS or AF I set them to null.  My code is:

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;

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;

Note both of these decisions may cause unwanted side effects.  For example, some clients like EG create a lot of global variables that may be used by the client, but do not start with SYS or AF.  

[Patrick]

Just be aware that this will also delete some of the global scope macro variable SAS creates during session invocation. 

You could as the very first step in a SAS session run the query and store away the global macro variables you find to then later on only delete the ones that didn't already exist at the beginning.

[Astounding]

SAS has published a way to do what  you ask:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p0j1htu10wsx9tn1mig5g0b8mxxb.htm

[mkeintz]

You could, as suggested by @Patrick delete all macrovars with scope='GLOBAL'.  But in some contexts, it's likely a lot of those macrovars were not explicitly defined by the user, and deletion might be counterproductive.

For instance, every time you submit code in a SAS/Studio session, it defines a number of macrovars with global scope - for instance SASWORKLOCATION.  If that macrovar is deleted in the middle of the submitted code, you might run into a problem later in the submission.  Of course this is not a problem if the macrovar deletions only occur as separate code submissions, or at the end of multi-step submissions.

An alternative would be to use a naming convention for all your macrovar names, say starting all the macrovar names with double underscores (SAS will already have some with single underscores).  Then you could filter on

    where scope='GLOBAL' and name EQT '__'

in the proc sql.