topic Re: Email a report only if the output has data in SAS Procedures

Email a report only if the output has data

nickb — Fri, 16 Jul 2010 17:17:39 GMT

I run some weekly data integrity checks that I'm moving to SAS. I want to put the output in a spreadsheet and email it to the contact. Is there a way that if the report returns 0 records, no spreadsheet and no email is generated?

I was thinking about first.

Thanks

Re: Email a report only if the output has data

sbb — Fri, 16 Jul 2010 17:58:55 GMT

Check the archives - one technique, you can to use the NOBS= keyword on the SET statement in a DATA step, and then use CALL EXECUTE to conditionally execute subsequent SAS code to generate the EMAIL, either with or without an attachment, considering possibly an EMAIL body text approach that states "no report today."

There are several approaches, some with using the SAS macro facility, and that avoid the facility - depending on the programmer's skill level and interest (or otherwise) with using macros and macro variables.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

set nobs condition sas code site:sas.com

Re: Email a report only if the output has data

ArtC — Fri, 16 Jul 2010 18:53:01 GMT

the following macro checks the number of obs in a data set. you may want to check that the data set exists first (EXIST function).
[pre]
%macro obscnt(dsn);
%local nobs;
%let nobs=.;

%* Open the data set of interest;
%let dsnid = %sysfunc(open(&dsn));

%* If the open was successful get the;
%* number of observations and CLOSE &dsn;
%if &dsnid %then %do;
%let nobs=%sysfunc(attrn(&dsnid,nlobs));
%let rc =%sysfunc(close(&dsnid));
%end;
%else %do;
%put Unable to open &dsn - %sysfunc(sysmsg());
%end;

%* Return the number of observations;
&nobs
%mend obscnt;
[/pre]

Re: Email a report only if the output has data

nickb — Thu, 19 Aug 2010 18:02:19 GMT

This appears to be working correctly but I need to figure out how NOT to send an email if nobs = 0. Right now the email is sent either way but if the condition is true, the body says "This is a test."

data _null_;
if %obscnt (work.Duplicate_Registration)>0 then
do;
file mail;
put "This is a test.";
end;
run; Message was edited by: nickb

Re: Email a report only if the output has data

garybald — Thu, 19 Aug 2010 19:18:03 GMT

I would make one small change to your code by checking for the empty dataset first.

data _null_;
if %obscnt (work.Duplicate_Registration) = 0 the stop;
else if %obscnt (work.Duplicate_Registration) > 0 then do;
file mail;
put "This is a test.";
end;
run;

Re: Email a report only if the output has data

data_null__ — Thu, 19 Aug 2010 21:38:50 GMT

The number of observations is irrelevant; everything you need can be easily obtained with a SET statement.

[pre]
data class;
set sashelp.class;
run;
data class;
modify class;
remove;
run;* cancel;
data _null_;
if _n_ eq 1 and eof then stop;
*file mail;
put "This is a test.";
stop;
set class(drop=_all_) end=eof;
run;
[/pre]

Re: Email a report only if the output has data

nickb — Fri, 20 Aug 2010 19:34:00 GMT

I've tried both approaches and if there are 0 records in the dataset the email is still being sent.

LIBNAME DWView META Library=DWView repname=Foundation host=coues user="&SPUsername" pw="&SPPassword";

FILENAME mail EMAIL
SUBJECT="Results as of %sysfunc(date(),yymmdd10.)"
TO= "myemail"
FROM= "myemail"
ATTACH="D:\Temp\nick_tmp\bad_grad_date.xls";
proc sql NOPRINT;
Create Table Work.bad_grad_date
as
select a.PERSON_ID_NB
,p.last_nm as HIGH_SCHOOL_NM
,i.CEEB_Cd as HIGH_SCHOOL_CD
,a.end_yr as Graduation_Dt
from (
SELECT piya.person_id, PERSON_ID_NB, piya.end_yr, max(institution_id) as institution_Id
FROM DWView.Person_Institution_Year_Attend_C PIYA
INNER JOIN (
select PIYA.person_Id, max(end_yr) AS GRAD_YR
from DWView.Person_Institution_Year_Attend_C PIYA
INNER JOIN DWVIEW.Institution_C I
on piYa.institution_id = i.institution_id
where i.education_type_cd in ('11','97','98')
and PIYA.END_YR NOT BETWEEN 1900 AND 2025
GROUP BY PIYA.PERSON_ID
) B
ON PIYA.Person_Id = B.Person_Id
AND B.Grad_Yr = PIYA.End_Yr
group by piya.person_id, piya.end_yr, PERSON_ID_NB
) a
inner join dwview.person_c p /* create join to get institution name */
on a.institution_Id = p.person_id
inner join dwview.institution_c i /* create join to get CEEB code */
on a.institution_id = i.institution_id
ORDER BY A.person_id_nb
;
quit;

PROC EXPORT
Data = Work.bad_grad_date
OUTFILE= "D:\Temp\nick_tmp\bad_grad_date.xls"
DBMS=XLS REPLACE;

data _null_;
if _n_ eq 0 and eof then stop;
file mail;
put "This is a test.";
stop;
set Work.bad_grad_date(drop=_all_) end=eof;
run;

Re: Email a report only if the output has data

sbb — Fri, 20 Aug 2010 19:49:13 GMT

The entire FILENAME EMAIL code piece cannot be executed to avoid having the EMAIL generated - you will need to consider using a SAS macro.

Scott Barry
SBBWorks, Inc.

Re: Email a report only if the output has data

data_null__ — Fri, 20 Aug 2010 21:55:25 GMT

> data _null_;
> if _n_ eq 0 and eof then stop;
> file mail;
> put "This is a test.";
> stop;
> set Work.bad_grad_date(drop=_all_) end=eof;
> run;

You have
[pre]if _n_ eq 0 and eof then stop;[/pre]

change 0 to 1.

Re: Email a report only if the output has data

Bill — Mon, 23 Aug 2010 13:20:54 GMT

an alternative ...

data _null_;
if 0 then set SerShip point=_n_ nobs=count;
call symput('COUNT',left(put(count,8.)));
stop;
run;

%macro mail;

%if &count > 0 %then %do;

filename mail email ' '
from="me@myplace.ca"
to="you@yourplace.us"
subject=
"Test Report";
;
data _null_;

file mail;
put "another SAS report for you";
run;

%end;

%mend mail;

%mail;

Re: Email a report only if the output has data

nickb — Mon, 23 Aug 2010 18:07:14 GMT

I'm looking for number of obs=0. I changed the value to 1 and it still emailing a spreadsheet that only contains headers. When I have the value set to 1, it emails the spreadsheet and the email body doesn't contain "This is a test" (that's what I would expect).

NOTE: Table WORK.BAD_GRAD_DATE created, with 0 rows and 4 columns.

Re: Email a report only if the output has data

sbb — Mon, 23 Aug 2010 20:08:06 GMT

Suggest you share your SAS-generated log with all relative code revealed - it's guess-work otherwise for forum subscribers.

Also, I strongly suggest you start with a very, very simple program, one that uses SASHELP.CLASS -- get that working and then broaden the approach to use your application data, but only after you have a clear grasp on what your code is doing and why.

Scott Barry
SBBWorks, Inc.

Re: Email a report only if the output has data

Cynthia_sas — Mon, 23 Aug 2010 21:10:44 GMT

And...in addition to Scott's (and others') most useful suggestions, I have a question that may take you in a slightly different direction.

I notice in your LIBNAME statement that you are using the Metadata Libname Engine and a REPNAME= option. Are you by any chance doing this emailing via a DI Studio job or a SAS Stored Process or via a batch job on one of the SAS platform servers (Workspace Server or Stored Process server)????

If you are creating output to be emailed via a job or a stored process, you might wish to refer to these Tech Support notes or documentation
http://support.sas.com/kb/39/220.html
http://support.sas.com/kb/17/867.html
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/stpsamp.html (which shows %let _RESULT=PACKAGE_TO_EMAIL;)

or this paper
http://support.sas.com/resources/papers/proceedings09/330-2009.pdf (which also shows emailing using PACKAGE_TO_EMAIL techniques)

or, otherwise, work with Tech Support on this question. You may find that emailing a file such as your Excel file requires that you create a SAS Package (like a zip file) and then email the SAS package to your email recipients. Tech Support can help you figure this out. Looking at the stored process examples should help you get an idea of how your code might need to be a bit different on the BI Platform.

cynthia

Re: Email a report only if the output has data

data_null__ — Mon, 23 Aug 2010 21:22:28 GMT

Yes I undstand now. You get e-mail with no matter if there are zero obs or not, just as you said but I didn't listen.

I did some testing with "FILE EMAIL" as I should have done to begin with. It seems that you can get it to work using FILEVAR option on FILE statement. Sorry to be so dense.

[pre]
data class;
set sashelp.class;
run;
data class;
modify class;
remove;
run;

data _null_;
if _n_ eq 1 and eof then stop;
to = 'datanull@gmail.com';
file dummy email filevar=to
subject="Results as of %sysfunc(date(),yymmdd10.)"
from='datanull@gmail.com'
attach='email02.sas'
;
stop;
set class(drop=_all_) end=eof;
run;
[/pre]

Re: Email a report only if the output has data

nickb — Tue, 24 Aug 2010 13:37:18 GMT

Works like a charm. Thanks again for all the help..

Re: Email a report only if the output has data

CryoKen — Fri, 29 Jul 2011 13:29:46 GMT

Works great and a great help - now I have to implement across hundreds of emails per day...thanks