Delete empty datasets....

[Xinxin]

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?

OR

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)

Thanks!

[Tom]

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 work.one ;

%end;

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')

;

&drop;