Help using Base SAS procedures

Delete empty datasets....

Posts: 35

Delete empty datasets....

I have a proc sql that joins two datasets. But if one of the datasets is empty (if the 1st one is empty, so will the 2nd one be), I don't want the Proc Sql to create a new table/dataset. Is there a way to put a condition to check if the data sets has any rows populated?


I want to delete empty datasets in a library before I 'set' them. Is there a way to delete only empty datasets from a library ?

(proc datasets deletes all or the ones specified...but I have to check which ones are empty before I delete them. This has to be an automated process)


Super User
Super User
Posts: 6,495

Delete empty datasets....

You can query metadata to determine the number of observations in a table.  You might want to watch out for deleted observations.

select nobs-delobs into :nobs from dictionary.tables where libname='WORK' and memname='ONE' ;

If you are creating a macro then you can use a %IF to generate the code.

%if &nobs %then %do;

  drop table ;


If you are not then you could generate the code into a macro variable.

%let drop=;

select 'drop table ' || catx('.',libname,memname) into :drop separated by ';'

  from dictionary.tables

    where nobs-delobs=0

     and libname='WORK' and memname in ('ONE','TWO')



Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation