Write a code to drop all blank datasets in a library

Reply
Occasional Contributor
Posts: 13

Write a code to drop all blank datasets in a library

Hello All,

 

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

 

Thanks in advance.

Regular Contributor
Posts: 228

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

[ Edited ]

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;

 

Occasional Contributor
Posts: 13

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

Thank you.. Smiley Happy

Super User
Posts: 789

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

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 142 views
  • 3 likes
  • 4 in conversation