11-17-2011 03:38 PM
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)
11-17-2011 04:26 PM
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 ;
If you are not then you could generate the code into a macro variable.
select 'drop table ' || catx('.',libname,memname) into :drop separated by ';'
and libname='WORK' and memname in ('ONE','TWO')