PROC DATASETS on empty library

[ErinKSimmons]

We have a bit of code (see below) that will output to a table in the work library a list of all tables in a user specific schema. Then will delete a table if it exists. This bit of code works well if the user's schema has tables in it, however some users are new and haven't created any tables so their schema is empty and PROC DATASETS generates the following warnings. 

WARNING: No matching members in directory.

WARNING: Output 'members' was not created.

How do I create and empty table, but with the same column names, when there are no members. Or is there a better way to generate a table full of dataset names for occupied and empty libraries.

libname MySchema oracle authdomain=oracle_auth path=database;

**List out Tables in MySchema to delete table if it exists;
ods output members=my_tables;
proc datasets library=MySchema;
quit;

data _null_;  

set my_tables;  

if upper(name) eq 'TEST' then do;    

call execute('proc delete data=MySchema.TEST;run;');  

end;
run;

[Quentin · Accepted solution]

I don't think there is an easy way to get ODS OUTPUT to create a 0 obs output dataset instead of not creating an output dataset.  And also don't think there is a way to suppress the warning message.

PROC CONTENTS with an OUT= option will create a 0 obs dataset, but it also throws a warning.

1    proc contents data=empty._all_ out=foo;
2    run ;

WARNING: No matching members in directory.
NOTE: The data set WORK.FOO has 0 observations and 41 variables.

If your goal is to basically do "if table exists then delete", maybe you could use PROC DATASETS to do the delete. With the NOWARN option it doesn't complain if a dataset doesn't exist.

libname empty "Q:\junk\empty" ;

proc datasets library=empty nolist nowarn;
  delete test;
quit;

Or, since PROC DELETE is often faster than PROC DATASETS, you could use a macro like (untested):

%macro deleteifexists(data) ;
  %if %sysfunc(exist(&data)) %then %do ;
    proc delete data=&data ;
    run ;
  %end ;
%mend deleteifexists ;

%deleteifexists(empty.test)

[ErinKSimmons]

Thanks, I used the macro approach solution. Your untested macro worked, for the record.

[Ksharp]

@Quentin 
No need a macro for PROC DELETE at all,
Just need one more option NODSNFRR .


options nodsnferr;
proc delete data=x;
run;

[Quentin]

Thanks @Ksharp , I didn't know about DSNFERR.  As I was thinking about this, I even thought "I wonder if there is something like DKRICOND=NOWARN but for datasets, bot variables" but I didn't bother to open up the documentation to look.

[Kurt_Bremser]

Instead of PROC DATASETS, use DICTIONARY.TABLES in SQL or SASHELP.VTABLE to scan for datasets:

data _null_;  
set sashelp.vtable;  
where libname = "MYSCHEMA" and memname = "TEST";
call execute(cats('proc delete data=',libname,'.',memname,';run;'));
run;