05-23-2017 11:47 AM - edited 05-23-2017 11:48 AM
some like this should work. Change the libname as per your requirement. check %put &dsn values;
proc sql noprint;
select memname into :dsn SEPARATED BY ','
where upcase(libname) ='WORK'
and nlobs = 0;
%do i = 1 %to %eval(%sysfunc(countc(%superq(dsn), %str(,)))+1);
drop table Work.%scan(%superq(dsn),&i,%str(','));
05-23-2017 02:12 PM
No need for a loop - @kiranv_'s good idea of "select ... from dictionary.tables" can be accompanied by a second statement in the same proc sql.
data mylib.one work.two; set sashelp.class; stop; run; proc sql noprint; select distinct catx('.',libname,memname) into :droplist separated by ',' from dictionary.tables where libname in ('WORK','MYLIB') and nlobs=0; drop table &droplist; quit;
However, @Ankur32's description in another forum suggests that "blank dataset" does NOT mean nlobs=0, but rather a dataset in which nlobs may be > 0 but that all the variables are always missing. Frankly I think "blank" should mean no rows of data. But if the alternative meaning is used, then the "nlobs=0" criterion will not work. One would have to scan all datasets in a library, to see which (among those with nlobs>0), if any, have only missing values.
05-23-2017 11:59 AM
1) Why do you have many datasets in your libraries that are empty and you don't know what these are?
2) Assuming by empty you mean zero observations:
data _null_; set sashelp.vtable (where=(libname="YOURLIB" and nobs=0)) end=last; if _n_=1 then call execute('proc datasets library=yourlib; delete '); call execute(name); if last then call execute('; quit; run;'); run;
Obviously replace the text yourlib with the library you are talking about. Then benefit of the above method is that only one procedure - datasets - is created and run, the given macro code in the other post will create x number of proc sql steps.