BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LFern
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

21 REPLIES 21
tomrvincent
Rhodochrosite | Level 12
Loop though them, deleting one at a time but skip var3.
Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

@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
Obsidian | Level 7
quick follow up, so there's no downside of using "from sashelp.vmacro" instead of "from dictionary.macros"?
Tom
Super User Tom
Super User
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
Obsidian | Level 7
Ahh, I see! Thanks for all your help!
Astounding
PROC Star

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
Obsidian | Level 7
Sorry, you're right. I was confusing "%put _all_" with %symdel. There is no %symdel _all_.
ballardw
Super User

@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
Quartz | Level 8

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
PROC Star
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
Quartz | Level 8
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
PROC Star

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 21 replies
  • 3941 views
  • 1 like
  • 6 in conversation