topic Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets in SAS Data Management

SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Mon, 29 Apr 2019 13:40:28 GMT

We have SAS V9 jobs running as part of daily batch and daily data sets will be updated.

but sometimes we have datasets with out data( having zero observations) getting updated assume even though it should update with records please )so datasets will be creating with no data.

We need to get e-mail automatically with datasets that are having zero observations.

please note that it is not related to specific library or specific data set we have this issue.this issue can occur in any library for any dataset which we need to take care of scenarios

please suggest the best way to do this.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

TomKari — Mon, 29 Apr 2019 14:33:13 GMT

While you wait for a DI-specific solution, here's a custom code option that will at least keep you going. It creates an empty dataset, and then the PROC SQL program puts the record count into a macro variable. If your SAS version is new enough to allow %if structures in open code, you can just use the piece underneath (substitute your sending the email for the "proc print", otherwise wrap it in a macro.

The %put statement isn't needed, I just added it for additional confirmation.

Tom

data Have;
	a = 1;
	stop;
run;

proc sql noprint;
	select count(*) into :RecCount from Have;
quit;

%put &=RecCount;

%if &RecCount = 0 %then
	%do;

		proc print data=sashelp.class;
		run;

	%end;

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Tue, 30 Apr 2019 12:53:17 GMT

Hi,
I don't want to create empty sas datset please.
I just need to get email that what are all the sas datasets with names
telling there are empty.so That I can verify later on why it has no data.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

TomKari — Tue, 30 Apr 2019 14:04:54 GMT

Okay, suppose the dataset you want to check is "SASUser.Have".

This code will do what you need, just substitute the code to send the email where this line is:

/* Code to send email about SASUser.Have */

Tom

proc sql noprint;
	select count(*) into :RecCount from SASUser.Have;
quit;

%if &RecCount = 0 %then
	%do;

		/* Code to send email about SASUser.Have */

	%end;

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Tue, 30 Apr 2019 15:16:17 GMT

Sorry Tom to confuse you.
Actually we don't know which datset can be created with 0 observations as
part of daily sas batch jobd.
Actually here we know already the name of dataset and library. Would you
please help me assuming I dont know the dataset name or library where we
have this case please. How can we handle this case please

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

TomKari — Tue, 30 Apr 2019 16:13:56 GMT

In that case, it will depend on whether there is a facility in SAS DI to do this check. I'm not aware of one; hopefully somebody more expert than me can help you.

Good luck!

Tom

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ChrisLysholm — Tue, 30 Apr 2019 19:30:06 GMT

Hello,

I would need some clarification from you regarding the specific problem you are facing prior to helping you find a suitable solution.

I believe you have a number of SAS libraries that contain data sets that you wish to monitor for zero observations?

Is this correct?

There are multiple SAS libraries involved but you do know prior to batch job execution which SAS libraries contain the data sets that need to be monitored for zero observations?

Is this correct?

You wish to trigger a single email message that lists those input/output tables that contain zero observations?

Is this correct?

Are you wishing to track input tables (tables that will update a target table) for zero observations or are you wishing to track the final updated target tables for zero observations?

Having a better understanding of the desired process flow will help me to locate a suitable solution.

Thanks very much.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ErikLund_Jensen — Wed, 01 May 2019 18:17:03 GMT

Hi @JJP1

A simple solution is to create a User Transformation in DI Studio with code to check the number of records and take appropriate action.

DI Studio creates a score of macro variables at the top of each transformation describing input and output, among these is the macro variable &_INPUT with the actual library.dataset name.

If the user transformation is added to a DI Studio job as the last transfromation with the table as input, you can use &_INPUT to refer to the data set in the transformation code.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Thu, 02 May 2019 06:51:08 GMT

Hi Erik,

Thanks for this.Would you please help on creating code for user written node transformation please as i am new to this.

Actually as part of this we need to check the record count of target dataset,if it is "0" then it should trigger e-mail saying "SAS dataset is updated with 0 observations"

if it is not zero then no need to send e-mail please.kindly help.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Thu, 02 May 2019 07:01:38 GMT

Hello Chris,

Please find my answers below

I believe you have a number of SAS libraries that contain data sets that you wish to monitor for zero observations?

Is this correct?

Yes,we need to be alerted via e-mail in SAS libraries that contain data sets where we have zero observations

There are multiple SAS libraries involved but you do know prior to batch job execution which SAS libraries contain the data sets that need to be monitored for zero observations?

Is this correct?

No i do not know prior/After batch job execution which SAS libraries contain the data sets that need to be monitored for zero observations.so only we would need e-mail to be triggered to us with names of data sets saying these are empty so that we can check later on that these are expected to be empty or not

You wish to trigger a single email message that lists those input/output tables that contain zero observations?

Is this correct?

Yes please ,wish to trigger a single email message that lists those input/output tables that contain zero observations

Are you wishing to track input tables (tables that will update a target table) for zero observations or are you wishing to track the final updated target tables for zero observations?

Yes,we wish to track the final updated target tables for zero observations in all SAS DI jobs.some input tables will be acting as taget tables in some other job,so it is basically final target table

Please let me know if you need further clarification and kindly help

Thanks very much.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ErikLund_Jensen — Thu, 02 May 2019 07:15:16 GMT

Hi @JJP1

I certainly will. I don't know if I can find time this evening, but you will have it before monday.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Thu, 02 May 2019 08:34:01 GMT

Hi Erik,

Yes,Thanks for your help.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ErikLund_Jensen — Thu, 02 May 2019 18:08:03 GMT

Hi @JJP1

I recommend the use of a transformation, because it works very well for us. This approach creates a mail for each table, because there is an instance of the transformation in every job we want to monitor that way. We have about 4000 DI Studio jobs in our baily batch, but if everything runs as expected, we don't get empty tables, so we are not bothered with that many mails.

We chose this approach, because our main concern was to make things easy for ETL developers. When they build a job, they just pull the transformation in and conncet it to the table, and then it works. It would be difficult to fulfill your request and get a single mail only, because the jobs runs after different schedules, so not all jobs are in the batch every day, and there has to be a final job that runs after all other jobs to collect the information and send the mail. It could be done, e.g. by coding the transformation to log each job/table in a database, and then find some way to make sure that the job reporting from the data base wasn't run before all other jobs had finished.

I have tried to write a simple transformation code, which is a cut-down version of our transformation. There might be some tricky things in the code if you are new to SAS, but the logical flow should be easy to follow. Note that mail addresses are hard coded and must be changed to something useful in your setup.

If you don't know how to write a user transformation, this is where you start:

And here is the code:

/***********************************************************************************/
/* MailEmptyDS                                                                     */
/*                                                                                 */
/* This transformation checks for zero observations in input data set and sends    */
/* an error mail.                                                                  */
/*                                                                                 */
/* The transformation works only with physical SAS tables (engine = V9 or BASE).   */
/* It is not possible to get record count from SAS Views or other engines, like    */
/* ODBC. In this case - or if the input data set has no physical existence - no    */
/* mail sent, and the transformation ends with a warning.                          */
/*                                                                                 */
/***********************************************************************************/
%macro MailEmptyDS(dummy);
	%local fejlstop libref dsid engnum rc engine path viewexist dsexist rcnt thisjob JobSlutDatetime;

	%put INFO: Check for zero rows in data set &_INPUT.;

	* Mail addresses;
	%let receiver = somebody@yoursite.xx;
	%let from = SAS Batch <batchaccount@yoursite.xx>

	* Get library engine;
	%let libref = %upcase(%scan(&_INPUT,1,%str(.)));
	%let dsid=%sysfunc(open(sashelp.vlibnam(where=(libname="&libref")),i));
	%if (&dsid ^= 0) %then %do;  
		%let engnum=%sysfunc(varnum(&dsid,ENGINE));
		%let rc=%sysfunc(fetch(&dsid));
		%let engine=%sysfunc(getvarc(&dsid,&engnum));
		%let rc= %sysfunc(close(&dsid.));
	%end;
	%put &=engine;

	* Check: is it a V9/BASE library? - else warn/return;
	%if &engine ne V9 and &engine ne BASE %then %do;
		%let path = %sysfunc(PATHNAME(&libref));
		%put WARNING: Library &libref (&path) is not a SAS BASE library - observation count not possible.;
		%let syscc = 4;
		%return;
	%end;

	* Check: Is it a view? - then warn/return;
	%let viewexist = %sysfunc(exist(&_INPUT,VIEW));
	%if &viewexist = 1 %then %do;
		%put WARNING: &_INPUT is a VIEW - observation count not possible.;
		%let syscc = 4;
		%return;
	%end;

	* Check: Does physical data set exist? - else warn/return;
	%let dsexist = %sysfunc(exist(&_INPUT));
	%if &dsexist = 0 %then %do;
		%put WARNING: Data set &_INPUT does not exist;
		%let syscc = 4;
		%return;
	%end;

	* Get observation count;
	%let rcnt = -1;
	%let dsid = %sysfunc(open(&_INPUT));
	%if &dsid %then %do;
		%let rcnt = %sysfunc(attrn(&dsid,NLOBS));
		%let rc = %sysfunc(close(&dsid));
	%end;
	%put &=rcnt;

	* Send mail if zero observations;
	%if &rcnt = 0 %then %do;
		filename outbox email 
			to = "&receiver"
			from="&from"
			subject="Warning - Empty data set created";

		data _null_;
			file outbox;
			put "Warning - Empty data set created";
			put "Table &_INPUT";
			put "Job &etls_jobName";
		run;
		%put INFO: Error mail sent to &receiver;
	%end;
	%else %put INFO: OK - data set &_INPUT has &rcnt observations.;

%mend;
%MailEmptyDS;

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

Patrick — Fri, 03 May 2019 01:47:14 GMT

@JJP1 I hope you fully appreciate how much @ErikLund_Jensen is helping you here.

@ErikLund_Jensen The following just as an ideas for code extensions.

1. I believe NLOBS is set to missing for any database table. You're already checking the library engine. Why not adding some logic to then execute a SQL count(*) for any non-SAS table?

2. DIS also generates some "&input_count" macro variable. Why not allow for multiple source tables and then just add a loop around your current code?

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ErikLund_Jensen — Fri, 03 May 2019 08:26:30 GMT

Hi @Patrick

Thanks for the ideas.

I tried to keep it simple, because i didn't want to scare @JJP1 off with too many new techniques, so please see my code as a "starter". Our transformation has several options:

Minimum acceptable record count with 0 as default.
Error action (abend/warn) with abend as default.
Send Mail (yes/no) - default: yes.
Optional Mail CC.
Optional text to include in the mail.

We didn't implement SQL record count, because we have a "best practice", where we create data as SAS tables in one job and load/update database tables in the next job. We use the transformation with error action=Abend in jobs creating SAS tables, so the LSF flow fails before the load job runs.

The looping idea is very good. I will implement that!

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Fri, 03 May 2019 08:45:22 GMT

Thank you so much Erik for helping me .Thanks for code and explanation and thanks for your time Erik.i will start creating the user written transformation.thanks

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

JJP1 — Fri, 03 May 2019 09:24:32 GMT

HI Erik,

I have created the user written code transformation job as you suggested and it is working perfectly.

Thanks for your help and i am just trying to understand the code that you written.

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ChrisLysholm — Fri, 03 May 2019 18:09:25 GMT

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;

Re: SAS DI : Job to check for empty datasets and e-mail all empty datasets

ChrisLysholm — Fri, 03 May 2019 18:11:17 GMT

Additionally, this code could be converted to generated transform, but I have not had time to produce that. Wanted to get you the basic code for the process first.