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

[JJP1]

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.

[TomKari]

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;

[JJP1]

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.

[TomKari]

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;

[JJP1]

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

[TomKari]

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

[ChrisLysholm]

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.

[JJP1]

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.

[ErikLund_Jensen]

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.

[JJP1]

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.

[ErikLund_Jensen]

Hi @JJP1 

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

[JJP1]

Hi Erik,

Yes,Thanks for your help.

[ErikLund_Jensen · Accepted solution]

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;

[Patrick]

@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?

[ErikLund_Jensen]

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!