Firstly, there is not a specific out-of-the box data integration studio transformation that will satisfy what you are trying to do. I have taken a first stab at producing SAS code that will produce a final output table which contains a row for each table that contains zero observations. This code will work with SAS library engine types as well as non SAS library engine types such as Oracle. I did not find time to write the email portion, but it seems like you have received other responses for that. In any case, my hope is that you will find some use for this code:
PROGRAM BEGINS:
/***
Program: notify_zero_obs.sas
Description:
Check for 0 observations in tables and notify when found.
***/
options
mprint
mlogic
symbolgen
;
/* Initialize macro variables */
%let rootDirPath = C:\communities\notify_zero_obs;
/* Initialize test data */
libname a_1 "&rootDirPath.\dir1";
libname a_2 "&rootDirPath.\dir2";
libname b_1 "&rootDirPath.\dir3";
data a_1.cars;
set sashelp.cars;
run;
data a_1.class;
set sashelp.class(obs=0);
run;
data a_2.snacks;
set sashelp.snacks;
run;
data a_2.stocks;
set sashelp.stocks(obs=0);
run;
/*
Build libname control table
Notes:
1. Some type of filtering where clause will be needed.
2. Only keeping variable libname in control table, but more could be selected
*/
proc datasets library=work
nolist
nowarn memtype = (data);
delete libnameControl;
quit;
proc sql;
create table work.libnameControl as
select distinct(libname), engine
from dictionary.libnames
where upcase(substr(libname,1,2)) = "A_";
quit;
/*
build table control table
*/
proc datasets library=work
nolist
nowarn memtype = (data);
delete tableControl;
quit;
/*
CAUTION:
There are timing considerations when using call execute function.
See this blog for timing issues -
https://blogs.sas.com/content/sgf/2017/08/02/call-execute-for-sas-data-driven-programming/
*/
%macro buildTableControl(libname=, engine=);
proc sql;
create table work.temp as
select *, "&engine." as engine length=25
from dictionary.tables
where upcase(libname) = "&libname.";
quit;
proc append base=work.tableControl
data=work.temp force;
run;
%mend;
data _null_;
set work.libnameControl;
call execute('%buildTableControl(libname=' || strip(libname) || ',' ||
'engine=' || strip(engine) || ');');
run;
/*
Update the table control table to add
observation count for Non SAS engines such as Oracle.
Note: cannot use call execute to drive update macro.
*/
%macro getObsCount(libname=, memname=);
proc sql noprint;
select count(*) into: obsCount
from &libname..&memname.;
quit;
proc sql;
update work.tableControl
set nobs=&obsCount.
where upcase(libname) = "&libname." and
upcase(memname) = "&memname.";
quit;
%mend;
proc datasets library=work
nolist
nowarn memtype = (data);
delete tableControlNonSAS;
quit;
proc sql;
create table work.tableControlNonSAS as
select *
from work.tableControl
where upcase(engine) ne "V9";
quit;
/* load macro variables */
data _null_;
set work.tableControlNonSAS end=eof;
call symputx('libname' || strip(put(_n_,best32.)),libname);
call symputx('memname' || strip(put(_n_,best32.)),memname);
if eof then
call symputx('numTables',strip(put(_n_,best32.)));
run;
%macro run_getObsCount();
%do i=1 %to &numTables.;
%getObsCount(libname=&&libname&i., memname=&&memname&i.);
%end;
%mend;
%run_getObsCount();
/* subset for zero obs */
data work.zeroObs;
set work.tableControl(where=(nobs=0));
run;
