topic Delete empty datasets.... in SAS Procedures

Delete empty datasets....

Xinxin — Thu, 17 Nov 2011 20:38:57 GMT

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)

Thanks!

Delete empty datasets....

Tom — Thu, 17 Nov 2011 21:26:00 GMT

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;