BookmarkSubscribeRSS Feed
nickb
Calcite | Level 5
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
15 REPLIES 15
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
ArtC
Rhodochrosite | Level 12
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]
nickb
Calcite | Level 5
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
garybald
Calcite | Level 5
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;
data_null__
Jade | Level 19
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]
nickb
Calcite | Level 5
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;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19
> 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.
nickb
Calcite | Level 5
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
data_null__
Jade | Level 19
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]
nickb
Calcite | Level 5
Works like a charm. Thanks again for all the help..
Bill
Quartz | Level 8
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 6111 views
  • 3 likes
  • 8 in conversation