topic Re: %symdel _all_ except in New SAS User

%symdel _all_ except

LFern — Fri, 26 Apr 2019 15:08:39 GMT

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.

Re: %symdel _all_ except

tomrvincent — Fri, 26 Apr 2019 17:59:16 GMT

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

Re: %symdel _all_ except

Tom — Fri, 26 Apr 2019 20:18:38 GMT

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;

Re: %symdel _all_ except

Astounding — Fri, 26 Apr 2019 20:01:22 GMT

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?

Re: %symdel _all_ except

ballardw — Fri, 26 Apr 2019 21:04:22 GMT

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

Re: %symdel _all_ except

smijoss1 — Sun, 28 Apr 2019 01:41:25 GMT

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;

Re: %symdel _all_ except

Astounding — Sun, 28 Apr 2019 08:03:16 GMT

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.

Re: %symdel _all_ except

smijoss1 — Sun, 28 Apr 2019 14:42:54 GMT

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

Re: %symdel _all_ except

Astounding — Sun, 28 Apr 2019 23:17:49 GMT

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.

Re: %symdel _all_ except

LFern — Mon, 29 Apr 2019 19:54:50 GMT

Hi there, thank you so much for the feedback! The delete_these dataset captures exactly what I want (to identify everything except var=3).

However, something is going wrong in the call execute function (see attached log). I tried to troubleshoot error 180-322 but wasn't able to find a fix for this specific situation.

Suggestions?

Re: %symdel _all_ except

Astounding — Mon, 29 Apr 2019 20:09:17 GMT

For starters, let's try inserting a STOP statement in the DATA _NULL_ step:

   data _null_;
      call execute ('%symdel ');
      do until (done);
         set delete_these end=done;
         call execute(name);
      end;
      call execute (';') ;
      STOP;
   run;

That's definitely needed, so let's see if that takes care of all the problems.

Re: %symdel _all_ except

Tom — Mon, 29 Apr 2019 20:32:45 GMT

Note sure why you need to worry about deleting macro variables (symbols). They aren't doing anybody any harm.

But your macro can be a lot simpler and more flexible.

Here is version that allows listing names to include, exclude or a prefix.

%macro delete_most(include,exclude,prefix);
%local list;
proc sql noprint;
  select name into :list separated by ' '
  from dictionary.macros
  where offset=0 and scope='GLOBAL'
%if %length(&prefix) %then 
    and name like %upcase(%bquote('&prefix%'))
;
%if %length(&include) %then 
    and findw("&include",name,' ','sit')
;
%if %length(&exclude) %then
    and not findw("&exclude",name,' ','sit')
;
  ;
quit;
%if &sqlobs %then %do;
  %symdel &list;
%end;
%mend delete_most;

Re: %symdel _all_ except

LFern — Tue, 30 Apr 2019 11:47:22 GMT

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

Re: %symdel _all_ except

LFern — Tue, 30 Apr 2019 12:03:19 GMT

Even with the stop, I'm getting the same 180-322 error as before:

NOTE: CALL EXECUTE generated line.

2 + VAR1

NOTE: Line generated by the CALL EXECUTE routine.

2 + VAR1

----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: %symdel _all_ except

LFern — Tue, 30 Apr 2019 13:27:25 GMT

I think I got something to work! I took the first part of your macro (where you create "delete_these") but used a different execute function that I found online:

----------------------------

%let VAR1 = 1;

%let VAR2 = 2;

%let VAR3 = 3;

%macro delvars(except=);

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

);%end;

run;

data _null_;

set delete_these;

if scope="GLOBAL" then call execute ('%symdel ' || trim(left(name))||';');

run;

%mend; %delvars(except=var3);

--------------------------

It worked for me as far as I can tell. %put _global_ now only returns var3.

Do you see anything obviously problematic?

Re: %symdel _all_ except

Tom — Tue, 30 Apr 2019 13:44:22 GMT

SAS did not like getting the %SYMDEL statement sent to it by CALL EXECUTE in multiple pieces. The first %SYMDEL code was interpreted by the macro processor and put nothing onto the stack of commands to run after the data step.

So it ended up thinking you are trying to run a command like:

VAR1 VAR2 ;

By sending a complete %SYMDEL statement in each CALL EXECUTE string you avoid that problem.

call execute(catx(' ','%symdel',name,';'));

You could also have fixed this by wrapping the original %SYMDEL code inside of %NRSTR() so that the %SYMDEL statement would be pushed onto the stack to execute after the data step.

1780  %put &=var1 &=var2 &=var3; ;
VAR1=x VAR2=x VAR3=x
1781  data _null_;
1782    if _n_=1 then call execute('%nrstr(%symdel) ');
1783    if eof then call execute(';');
1784    set test end=eof;
1785    call execute(name);
1786  run;

NOTE: There were 3 observations read from the data set WORK.TEST.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
1    + %symdel
2    + var1
3    + var2
4    + var3
5    + ;
1787  %put &=var1 &=var2 &=var3; ;
WARNING: Apparent symbolic reference VAR1 not resolved.
WARNING: Apparent symbolic reference VAR2 not resolved.
WARNING: Apparent symbolic reference VAR3 not resolved.
var1 var2 var3

Re: %symdel _all_ except

LFern — Tue, 30 Apr 2019 19:04:21 GMT

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

Re: %symdel _all_ except

Tom — Tue, 30 Apr 2019 19:32:16 GMT

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

Re: %symdel _all_ except

LFern — Tue, 30 Apr 2019 19:35:32 GMT

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

Re: %symdel _all_ except

Tom — Tue, 30 Apr 2019 19:39:17 GMT

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.