%symdel _all_ except

[LFern]

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.

[tomrvincent]

Loop though them, deleting one at a time but skip var3.

[Tom · Accepted solution]

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;

[LFern]

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"?

[Tom]

---

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

[LFern]

quick follow up, so there's no downside of using "from sashelp.vmacro" instead of "from dictionary.macros"?

[Tom]

SASHELP.VMACRO is just a view to DICTIONARY.MACROS.
Unlike some other DICTIONARY tables there is no downside to using the SASHELP view since there are no performance issues with SASHELP.VMACRO.

[LFern]

Ahh, I see! Thanks for all your help!

[Astounding]

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]

Sorry, you're right. I was confusing "%put _all_" with %symdel. There is no %symdel _all_.

[ballardw]

---

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

[smijoss1]

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; 

[Astounding]

There are definitely some useful features here.

Rather than specifying a prefix, this application needs to specify a list of macro variables to skip over.

[smijoss1]

All my temp macro vars are prefixed with 1 or 2 underscore. So it suits my needs.. bht feel free to modify to your needs

[Astounding]

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.