topic Re: Write a code to drop all blank datasets in a library in SAS Programming

Write a code to drop all blank datasets in a library

Ankur32 — Tue, 23 May 2017 15:32:04 GMT

Hello All,

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

Thanks in advance.

Re: Write a code to drop all blank datasets in a library

kiranv_ — Tue, 23 May 2017 15:48:43 GMT

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;

Re: Write a code to drop all blank datasets in a library

Ankur32 — Tue, 23 May 2017 15:51:28 GMT

Thank you.. 🙂

Re: Write a code to drop all blank datasets in a library

RW9 — Tue, 23 May 2017 15:59:07 GMT

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.

Re: Write a code to drop all blank datasets in a library

mkeintz — Tue, 23 May 2017 18:12:01 GMT

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.