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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.