Lesson 9, Part 7: Deleting User-defined Macro Variables¶

  • %SYMDEL statement in open code
  • %SYMDEL statement in a macro from
    • Dictionary.Macros
    • SASHELP.VMACRO (automatically generated view that contains a list all macro variables existing in the current session as well as those defined by the SAS System)
Create a macro variable in OPEN CODE¶
In [6]:
ods html close;
options nodate nonumber nonotes nosource;
%LET course = STAT6197;
%put &=course;
%put _user_;

                                                           The SAS System

COURSE=STAT6197
GLOBAL COURSE STAT6197
                                                           The SAS System

E3969440A681A2408885998500000008
Delete the macro variable created above using %SYMDEL statement¶
In [8]:
ods html close;
options nodate nonumber nonotes nosource;
%SYMDEL course /nowarn;
%put _user_;

                                                           The SAS System

E3969440A681A2408885998500000010
Create a vertical list of macro variables and display them¶
In [10]:
ods html close;
options nodate nonumber nonotes nosource;
ods html close;
%let Put_title = List of Values into a Series of Macro Variables;
proc sql noprint;
 select distinct make
        INTO :makes1-
 FROM SASHELP.CARS ;
 %put Number of Rows: &sqlobs;
quit;
%macro runit;
 %put &Put_title;
 %Do i=1 %TO &Sqlobs;
    %put &&makes&i;
  %end;
%mend runit;
%runit

                                                           The SAS System

Number of Rows: 3
List of Values into a Series of Macro Variables
Ford
GMC
Hummer
                                                           The SAS System

E3969440A681A2408885998500000012

Deleting global macro variables by Jim Simon

In [12]:
ods html close;
options nodate nonumber nonotes nosource;
%macro deleteALL;
 options nonotes;
 %local vars;
 proc sql noprint;
        select name into: vars separated by ' '
           from dictionary.macros /* DICTIONARY.MACROS is a metadata table 
                                   with one row per macro variable.*/
   where scope='GLOBAL' 
   and not name contains 'SYS_SQL_IP_';
   quit;
    %symdel &vars;  /* The %SYMDEL statement deletes all user-defined global 
                    macro variables.*/
    options notes;
     %put NOTE: Macro variables deleted.;
 %mend deleteALL;
 %deleteALL
 %put _user_;

                                                           The SAS System

NOTE: Macro variables deleted.
GLOBAL SYS_SQL_IP_ALL -1
GLOBAL SYS_SQL_IP_STMT 
                                                           The SAS System

E3969440A681A2408885998500000014
Recreate a vertical list of macro variables and display them¶
In [14]:
ods html close;
options nodate nonumber nonotes nosource;
ods html close;
%let Put_title = List of Values into a Series of Macro Variables;
proc sql noprint;
 select distinct make
        INTO :makes1-
 FROM SASHELP.CARS ;
 %put Number of Rows: &sqlobs;
quit;

                                                           The SAS System

NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1
Number of Rows: 3
                                                           The SAS System

E3969440A681A2408885998500000016
DESCRIBE TABLE statement with PROC SQL¶

Since you have recreated the macro variables, you can run the following code to list the names of the columns stored in the DICTIONARY.MACROS entry.

In [16]:
ods html close;
options nodate nonumber nonotes nosource;
ods html close;
proc sql;
  describe table dictionary.macros;
quit;

                                                           The SAS System


create table DICTIONARY.MACROS
  (
   scope char(32) label='Macro Scope',
   name char(32) label='Macro Variable Name',
   offset num label='Offset into Macro Variable',
   value char(200) label='Macro Variable Value'
  );

                                                           The SAS System

E3969440A681A2408885998500000018

How to print the data values of the MACROS entry¶

  • Make a SAS data set containing the extract of the Macro entry from the current session using PROC SQL CREATE table

  • List the SAS data set using PROC PRINT

In [19]:
ods html close;
options nodate nonumber nonotes nosource;
proc sql;
create table work.MyMacros as
select * from dictionary.macros;
where name ='GLOBAL';
quit;
proc print data=work.MyMacros;
Title 'Work.Mymacros';
run;
SAS Output

Work.Mymacros

Obs scope name offset value
1 GLOBAL MAKES1 0 Ford
2 GLOBAL MAKES2 0 GMC
3 GLOBAL MAKES3 0 Hummer
4 GLOBAL PUT_TITLE 0 List of Values into a Series of Macro Variables
5 GLOBAL SQLEXITCODE 0 0
6 GLOBAL SQLOBS 0 0
7 GLOBAL SQLOOPS 0 0
8 GLOBAL SQLRC 0 0
9 GLOBAL SQLXOBS 0 0
10 GLOBAL SQLXOPENERRS 0 0
11 GLOBAL SYS_SQL_IP_ALL 0 -1
12 GLOBAL SYS_SQL_IP_STMT 0  
13 AUTOMATIC AFDSID 0 0
14 AUTOMATIC AFDSNAME 0  
15 AUTOMATIC AFLIB 0  
16 AUTOMATIC AFSTR1 0  
17 AUTOMATIC AFSTR2 0  
18 AUTOMATIC FSPBDV 0  
19 AUTOMATIC SYSADDRBITS 0 64
20 AUTOMATIC SYSBUFFR 0  
21 AUTOMATIC SYSCC 0 3000
22 AUTOMATIC SYSCHARWIDTH 0 1
23 AUTOMATIC SYSCMD 0  
24 AUTOMATIC SYSDATASTEPPHASE 0  
25 AUTOMATIC SYSDATE 0 26JUN26
26 AUTOMATIC SYSDATE9 0 26JUN2026
27 AUTOMATIC SYSDAY 0 Friday
28 AUTOMATIC SYSDEVIC 0  
29 AUTOMATIC SYSDMG 0 0
30 AUTOMATIC SYSDSN 0 _NULL_
31 AUTOMATIC SYSENCODING 0 wlatin1
32 AUTOMATIC SYSENDIAN 0 LITTLE
33 AUTOMATIC SYSENV 0 BACK
34 AUTOMATIC SYSERR 0 0
35 AUTOMATIC SYSERRORTEXT 0 180-322: Statement is not valid or it is used out of proper order.
36 AUTOMATIC SYSFILRC 0 0
37 AUTOMATIC SYSHOSTINFOLONG 0 X64_10PRO WIN 10.0.19041 Workstation
38 AUTOMATIC SYSHOSTNAME 0 CCASTA-HWJP0G2
39 AUTOMATIC SYSINCLUDEFILEDEVICE 0  
40 AUTOMATIC SYSINCLUDEFILEDIR 0  
41 AUTOMATIC SYSINCLUDEFILEFILEREF 0  
42 AUTOMATIC SYSINCLUDEFILENAME 0  
43 AUTOMATIC SYSINDEX 0 2
44 AUTOMATIC SYSINFO 0 0
45 AUTOMATIC SYSJOBID 0 145876
46 AUTOMATIC SYSLAST 0 _NULL_
47 AUTOMATIC SYSLCKRC 0 0
48 AUTOMATIC SYSLIBRC 0 0
49 AUTOMATIC SYSLOGAPPLNAME 0  
50 AUTOMATIC SYSMACRONAME 0  
51 AUTOMATIC SYSMAXLONG 0 2147483647
52 AUTOMATIC SYSMENV 0  
53 AUTOMATIC SYSMSG 0  
54 AUTOMATIC SYSNCPU 0 4
55 AUTOMATIC SYSNOBS 0 3
56 AUTOMATIC SYSODSESCAPECHAR 0 03
57 AUTOMATIC SYSODSGRAPHICS 0 1
58 AUTOMATIC SYSODSPATH 0 SASUSER.TEMPLAT(UPDATE) SASHELP.TMPLMST(READ)
59 AUTOMATIC SYSPARM 0  
60 AUTOMATIC SYSPRINTTOLOG 0  
61 AUTOMATIC SYSPRINTTOLIST 0  
62 AUTOMATIC SYSPROCESSID 0 41DF43B26CEFEF9E4018000000000000
63 AUTOMATIC SYSPROCESSMODE 0 SAS Workspace Server
64 AUTOMATIC SYSPROCESSNAME 0 Object Server
65 AUTOMATIC SYSPROCNAME 0 SQL
66 AUTOMATIC SYSRC 0 0
67 AUTOMATIC SYSSCP 0 WIN
68 AUTOMATIC SYSSCPL 0 X64_10PRO
69 AUTOMATIC SYSSITE 0 70158136
70 AUTOMATIC SYSSIZEOFLONG 0 4
71 AUTOMATIC SYSSIZEOFPTR 0 8
72 AUTOMATIC SYSSIZEOFUNICODE 0 2
73 AUTOMATIC SYSSTARTID 0  
74 AUTOMATIC SYSSTARTNAME 0  
75 AUTOMATIC SYSTCPIPHOSTNAME 0 CCASTA-HWJP0G2
76 AUTOMATIC SYSTIME 0 19:25
77 AUTOMATIC SYSTIMEZONE 0  
78 AUTOMATIC SYSTIMEZONEIDENT 0  
79 AUTOMATIC SYSTIMEZONEOFFSET 0 -14400
80 AUTOMATIC SYSUSERID 0 muhuri
81 AUTOMATIC SYSVER 0 9.4
82 AUTOMATIC SYSVLONG 0 9.04.01M7P080520
83 AUTOMATIC SYSVLONG4 0 9.04.01M7P08052020
84 AUTOMATIC SYSWARNINGTEXT 0 Apparent invocation of macro WHO_LS not resolved.
SASHELP.VMACRO¶
  • Use SASHELP.VMACRO to keep track of all macro variables you have created

Surviving the SAS® Macro Jungle by Using Your Own Programming Toolkit by Kevin Russell

In [ ]:
proc print data=sashelp.vmacro(where=(name=:'MAKE'));
run;
SASHELP.VMACRO and %SYMDEL Statement¶

Kevin Russell (2016) uses the following code to delete all user-defined global macro variables.

In [21]:
ods html close;
options nodate nonumber nonotes nosource;
%macro delvars;
data vars;
 set sashelp.vmacro;
 run;
data _null_;
 set vars;
 temp=lag(name);
 if scope='GLOBAL' and substr(name,1,3) ne 'SYS' and temp ne name then
 rc=dosubl('%symdel '||trim(left(name))||';');
 run;
%mend;
%delvars
%put _user_;

                                                           The SAS System

GLOBAL SYS_SQL_IP_ALL -1
GLOBAL SYS_SQL_IP_STMT 
                                                           The SAS System

E3969440A681A2408885998500000023

A Hands-on Introduction to SAS® Metadata DICTIONARY Tables and SASHELP Views

In [ ]:
*https://blogs.sas.com/content/sastraining/2018/05/07/deleting-global-macro-variables/;
%macro deleteALL;
   options nonotes;
   %local vars;
   proc sql noprint;
           select name into: vars separated by ' '
           from dictionary.macros
                 where scope='GLOBAL' 
     and not name contains 'SYS_SQL_IP_';
   quit;
   %symdel &vars;
   options notes;
   %put NOTE: Macro variables deleted.;
%mend deleteALL;
%deleteAll

The macro below deletes all global macro variables except those containing 'SYS_SQL_IP_'. SAS system-defined macro variables and those dynamically created by SQL queries should be preserved.

In [ ]:
%macro deleteALL; /* Defines the macro deleteALL.*/
    options nonotes; /* Suppresses log notes to keep the output clean. */
    %local varCount i varName; /* Declares macro variables used within the macro */
    
    /* Count the number of global macro variables */
    proc sql noprint;
        select count(*) into :varCount
                  /* The macro variable varCount → Stores the number of 
                     global macro variables to delete.*/
        from dictionary.macros
        where scope = 'GLOBAL' and not name contains 'SYS_SQL_IP_';
    quit;
    
    /* Loop through and delete each macro variable individually */
    %if &varCount > 0 %then %do;Filters only global macro variables
        proc sql noprint; /* Runs an SQL query without displaying output in the log */
            select name into :varName1-:varName&varCount
              from dictionary.macros
               /* Stores macro variable names retrieved from dictionary.macros.*/
            where scope = 'GLOBAL' and not name contains 'SYS_SQL_IP_';
             /* Filters only global macro variables, excluding system-generated 
             SQL macro variables.*/
        quit;

        %do i = 1 %to &varCount;
            %symdel &&varName&i;                      
        %end;
        
        /* Iterates from 1 to varCount.
        &&varName&i → Resolves dynamically to varName1, varName2, etc.
        %symdel &&varName&i; → Deletes each macro variable individually.
        */
    %end;

    options notes;
    %put NOTE: Macro variables deleted.;
%mend deleteALL;

%deleteALL;
How the above program works¶
  • Finds all global macro variables (except those containing 'SYS_SQL_IP_').
  • Counts how many need to be deleted.
  • Retrieves their names into multiple macro variables.
  • Loops through and deletes each one using %symdel.
  • Prints a message confirming deletion.