topic Re: Delete empty datasets from work lib in SAS Programming

Delete empty datasets from work lib

cxkev — Wed, 12 Jun 2013 12:56:29 GMT

Goof Afternoon,

Does anyone know if it is possible to have a procedure or macro that will delete all empty datasets (ie dataset that has 0 obs) in the work library?

Many thanks.

cxkev

Re: Delete empty datasets from work lib

Tom — Wed, 12 Jun 2013 13:44:45 GMT

You can query DICTIONARY.TABLES to find the members with 0 obs.

You could use the information to generate DROP TABLE statements.

proc sql noprint ;

select 'drop table work.'||memname into :empties separated by ';'

from dictionary.tables

where libname='WORK' and nobs=0

;

&empties;

quit;

Re: Delete empty datasets from work lib

cxkev — Wed, 12 Jun 2013 14:36:16 GMT

Thaks Tom - Do I need to code the macro &empties, do you know how I would go about that?

Just copied and pasted your code and got "apparent symbolic reference EMPTIES not resolved"

Apols if I'm missing somethign obvious here.

Thanks,

cxkev

Re: Delete empty datasets from work lib

cxkev — Wed, 12 Jun 2013 14:38:31 GMT

Actually Tom - I can go from here - I was not aware of DICTIONARY.TABLES before but now I have that I know where to go.

Thanks very much for your help.

cxkev

Re: Delete empty datasets from work lib

Tom — Wed, 12 Jun 2013 14:57:00 GMT

Add this line before the SELECT statement to clear the macro variable and make sure it is defined and empty even when there are no results returned from the query.

%let empties=;

Re: Delete empty datasets from work lib

cxkev — Thu, 13 Jun 2013 08:14:00 GMT

Thanks Tom - That's worked a treat!