Suppose I have 3 %let variables:
%let var1 = 1;
%let var2 = 2;
%let var3 = 3;
I know %symdel _all_ will erase all three. How do I add an exception to the "all"? I want to be able to delete all except var3, for example.
Make your own list of symbols (macro variables) to delete from the list of existing.
%let var1=x;
%let var2=y;
%let var3=z;
proc sql noprint;
select distinct name into :symlist separated by ' '
from dictionary.macros
where scope='GLOBAL'
and name like 'VAR%'
and not name='VAR3'
;
%put &=var1 &=var2 &=var3;
%symdel &symlist;
%put &=var1 &=var2 &=var3;
Make your own list of symbols (macro variables) to delete from the list of existing.
%let var1=x;
%let var2=y;
%let var3=z;
proc sql noprint;
select distinct name into :symlist separated by ' '
from dictionary.macros
where scope='GLOBAL'
and name like 'VAR%'
and not name='VAR3'
;
%put &=var1 &=var2 &=var3;
%symdel &symlist;
%put &=var1 &=var2 &=var3;
Hi Tom,
I really like this code!
I wasn't sure how to work with %put functions (i.e., I have no idea what it does) so I modified your code slightly and got this error:
-------------
%let var1=1;
%let var2=2;
%let var3=3;
proc sql noprint;
select distinct name into :symlist separated by ' '
from dictionary.macros
where scope='GLOBAL'
and not name='var3'
;quit;
%symdel &symlist;
ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_ALL.
ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_STMT.
-------------
I'm guessing that the "sys_sql" are automatically created during the sql procedure. I suppose I could work around this error by including 2 more "and not name=" lines that capture both "sys_sql" macro variables.
Is there a downside of using "from sashelp.vmacro" instead of "from dictionary.macros"?
@LFern wrote:
...
ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_ALL.
ERROR: Attempt to delete automatic macro variable SYS_SQL_IP_STMT.
...
SAS has a lot that right hand not knowing what the left hand is doing type of problems. If there were AUOTMATIC macro variables they should have been made with SCOPE='AUTOMATIC' instead of SCOPE='GLOBAL'.
Yes if you really made a macro you used a lot you might want to make sure not to attempt to delete those two macro variables.
You might want expand the exclusion list to not try to eliminate any macro variables that start with SYS.
I must have missed something along the way.
Since when does %SYMDEL accept lists of macro variables such as _ALL_ ? Last I checked, you had to spell out the names of all macro variables that you want deleted.
Either way, this can be accomplished, writing a macro such as:
%delete_most (except=var3)
But the way you go about it depends on how %SYMDEL _ALL_ behaves. Have you tried that statement, and what were the results?
@LFern wrote:
Suppose I have 3 %let variables:
%let var1 = 1;
%let var2 = 2;
%let var3 = 3;
I know %symdel _all_ will erase all three. How do I add an exception to the "all"? I want to be able to delete all except var3, for example.
74 %symdel _all_; WARNING: Attempt to delete macro variable _ALL_ failed. Variable not found.
@Astounding is correct as far as SAS 9.4 TS Level 1M4 goes.
If that actually works for you to remove multiple macro variables then please describe the environment you are using.
Heres the macro i use to clean up macro vars.
you can change it to suit it to your needs.
%Macro Clean_Up_Macro_Vars(prefix);
/*****************************************************************/
/* Delete macro variables created by previous Run */
/*****************************************************************/
%nrstr(options nosource nomprint nonotes;);
/* Create list of macro variables to be deleted */
proc sort data=sashelp.vmacro nodupkey out=Temp_dsn;
by name;
where name =: upcase("&prefix.");
run;
data _null_;
set Temp_dsn;
/* Execute %SymDel against each Macro variable */
if scope='GLOBAL' then
call execute('%symdel ' ||trim(left(name))||';');
run;
proc delete data=Temp_dsn;run;
%nrstr(options source mprint notes;);
%Mend;
OK, here goes. It is expected that the macro will be called along these lines:
%delete_most (except=var3 var5)
%macro delete_most (except=);
%local k;
proc sort data=sashelp.vmacro nodupkey out=delete_these;
by name;
where scope='GLOBAL' and upcase(name) not in
( %do k = 1 %to %sysfunc(countw(&except) ) ;
"%upcase(%scan(&except, &k))"
);
run;
data _null_;
call execute ('%symdel ');
do until (done);
set delete_these end=done;
call execute(name);
end;
call execute (';') ;
run;
%mend delete_most;
It's untested code, and so might need a slight tweak. But it should be about right.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.