topic Re: %let catx call execute in SAS Programming

%let catx call execute

mansour_ibrahim — Wed, 04 Apr 2018 11:03:52 GMT

Hello,

I want to have the list of tables that do not contain data with this method.

A can't make the "retain" of the macro variable table1

Thank you for your help.

%macro tab(mem=);
%global table1 ;
%let table1=;
%local table;
%let table=;
%let dsid  = %sysfunc(open(&mem.,in));
%let nbr   = %sysfunc(attrn(&dsid,nobs));
%if &nbr.=0 %THEN %do; 
%let table =&mem.;
%let table1=%sysfunc(catx('-',&table,&table1));
%end;
%if &dsid. > 0 %then 


%let rc = %sysfunc(close(&dsid));
%mend tab;


DATA _null_;
SET vcol;
call execute('%tab(mem='||strip(memname)||');');
run;

%Put table1=&table1;

Re: %let catx call execute

Kurt_Bremser — Wed, 04 Apr 2018 11:45:27 GMT

why not retrieve the nobs from dictionary.tables?

do this

%let table1=&table.-&table1;

instead of

%let table1=%sysfunc(catx('-',&table,&table1));

Re: %let catx call execute

andreas_lds — Wed, 04 Apr 2018 12:00:52 GMT

You reset &table1 each time the macro is called

%let table1=;

To solve this remove

%global table1 ;
%let table1=;

from the macro and put them before the data-step.

Even better: use dictionary.tables, as suggested by @Kurt_Bremser.

The following code is untested, because i don't know what you have in dataset "vcol":

Re: %let catx call execute

andreas_lds — Wed, 04 Apr 2018 12:01:48 GMT

unable to add the missing code to my last post, so here it is:

proc sql noprint;
   select MemName
      into :table1 separated by '-'
      from sashelp.vtable
         where nobs = 0 and MemName in (
            select MemName from work.vcol
         )
   ;
quit;

%put &=table1;