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

 

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User
  1. why not retrieve the nobs from dictionary.tables?
  2. do this 
    %let table1=&table.-&table1;
     instead of
    %let table1=%sysfunc(catx('-',&table,&table1));
andreas_lds
Jade | Level 19

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

 

andreas_lds
Jade | Level 19

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 895 views
  • 2 likes
  • 3 in conversation