DATA Step, Macro, Functions and more

Delete empty datasets from work lib

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Delete empty datasets from work lib

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


Accepted Solutions
Solution
‎06-12-2013 09:44 AM
Super User
Super User
Posts: 6,502

Re: Delete empty datasets from work lib

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;

View solution in original post


All Replies
Solution
‎06-12-2013 09:44 AM
Super User
Super User
Posts: 6,502

Re: Delete empty datasets from work lib

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;

Contributor
Posts: 28

Re: Delete empty datasets from work lib

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


Contributor
Posts: 28

Re: Delete empty datasets from work lib

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

Super User
Super User
Posts: 6,502

Re: Delete empty datasets from work lib

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=;

Contributor
Posts: 28

Re: Delete empty datasets from work lib

Thanks Tom - That's worked a treat!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1494 views
  • 5 likes
  • 2 in conversation