Help using Base SAS procedures

Email a report only if the output has data

Reply
Contributor
Posts: 65

Email a report only if the output has data

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Email a report only if the output has data

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
Valued Guide
Posts: 632

Re: Email a report only if the output has data

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]
Contributor
Posts: 65

Re: Email a report only if the output has data

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
Contributor
Posts: 29

Re: Email a report only if the output has data

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;
Respected Advisor
Posts: 3,777

Re: Email a report only if the output has data

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]
Contributor
Posts: 65

Re: Email a report only if the output has data

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;
Super Contributor
Super Contributor
Posts: 3,174

Re: Email a report only if the output has data

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.
Respected Advisor
Posts: 3,777

Re: Email a report only if the output has data

> 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.
Contributor
Posts: 65

Re: Email a report only if the output has data

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Email a report only if the output has data

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.
SAS Super FREQ
Posts: 8,743

Re: Email a report only if the output has data

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_EMAILSmiley Wink

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
Respected Advisor
Posts: 3,777

Re: Email a report only if the output has data

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]
Contributor
Posts: 65

Re: Email a report only if the output has data

Works like a charm. Thanks again for all the help..
Super Contributor
Posts: 291

Re: Email a report only if the output has data

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;
Ask a Question
Discussion stats
  • 15 replies
  • 2003 views
  • 2 likes
  • 8 in conversation