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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.