Write a code to drop all blank datasets in a library

[Ankur32]

Hello All,

Can you please help me to Write a code "to drop all blank datasets in a library".

Thanks in advance.

[kiranv_]

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 ','

from dictionary.tables

where upcase(libname) ='WORK'

and nlobs = 0;

QUIT;

　

　

%put &dsn;

　

%macro abc;

%do i = 1 %to %eval(%sysfunc(countc(%superq(dsn), %str(,)))+1);

proc sql;

drop table Work.%scan(%superq(dsn),&i,%str(','));

quit;

%end;

%mend abc;

%abc;

[Ankur32]

Thank you.. 🙂

[mkeintz]

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.

[RW9]

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.