topic Do not send email if no data in SAS Programming

Do not send email if no data

LAtwood — Wed, 11 Feb 2015 20:40:29 GMT

If a sas dataset has no data I do not want to send an email using the following code. I know I need to use a macro, but I'm not very good at the macro language.

The email attaches the SAS dataset which I first export to a csv file. If there is no data I would like it to not send the email.

My code:

proc export data=work.mydata

outfile='D:\folder\test\mydata.csv'

dbms = csv replace;

delimiter=';' ;

run;

filename mymail email "me.me@email.com"

subject="My Mail to You"

attach='D:\folder\test\mydata.csv';

data _null_;

file mymail;

run;

Re: Do not send email if no data

PoornimaRavishankar — Wed, 11 Feb 2015 20:46:27 GMT

%macro runornot(count);

%if &count NE 0 %then %do;

proc export data=work.mydata

outfile='D:\folder\test\mydata.csv'

dbms = csv replace;

delimiter=';' ;

run;

filename mymail email "me.me@email.com"

subject="My Mail to You"

attach='D:\folder\test\mydata.csv';

data _null_;

file mymail;

run;

%end;

%mend runornot;

data _null_;

set work.mydata end=eof;

if eof then call symput('count',_N_);

run;

%runornot(&count);

Re: Do not send email if no data

LAtwood — Wed, 11 Feb 2015 21:10:17 GMT

I get the following Warning and Error

WARNING: Apparent symbolic reference COUNT not resolved.

ERROR: The text expression &COUENT contains a recursive reference to the macro variable COUNT. The macro variable will be assigned the null value.

Re: Do not send email if no data

PoornimaRavishankar — Wed, 11 Feb 2015 21:15:52 GMT

%macro runornot(total=);

%if &total NE 0 %then %do;

proc export data=work.mydata

outfile='D:\folder\test\mydata.csv'

dbms = csv replace;

delimiter=';' ;

run;

filename mymail email "me.me@email.com"

subject="My Mail to You"

attach='D:\folder\test\mydata.csv';

data _null_;

file mymail;

run;

%end;

%mend runornot;

data _null_;

set work.mydata end=eof;

if eof then call symput('count',_N_);

run;

%runornot(total=&count);

Re: Do not send email if no data

Ksharp — Thu, 12 Feb 2015 08:10:44 GMT

You could use ATTRN() to check if the dataset have obs or variable.

ANY

specifies whether the data set has observations or variables.

−1 the data set has no observations or variables.

0 the data set has no observations.

1 the data set has observations and variables.

data class;

set sashelp.class;

stop;

run;

%let dsid=%sysfunc(open(class));

%let any=%sysfunc(attrn(&dsid,any));

%let dsid=%sysfunc(close(&dsid));

%put &any ;

Xia Keshan

Re: Do not send email if no data

PoornimaRavishankar — Thu, 12 Feb 2015 14:01:41 GMT

Sorry I solved the wrong problem. My solution will not work since the eof condition will never be satisfied. I believe once SAS 'detects' that the dataset has no observations, it doesn't iterate and hence will never reach end of file. This also means that this information is stored in some dataset metadata. Use xia keshan's solution.

Re: Do not send email if no data

RW9 — Thu, 12 Feb 2015 14:27:16 GMT

Just pop your code in a call execute:

data _null_;

set sashelp.vtable (where=(libname="WORK" and memname="MYDATA" and nobs>0));

call execute('filename mymail email "me.me@email.com"

subject="My Mail to You"

attach='D:\folder\test\mydata.csv';

data _null_;

file mymail;

run;');

run;

That way if there are no observations, then the call execute statement never generates.

Re: Do not send email if no data

LAtwood — Thu, 12 Feb 2015 14:50:43 GMT

When I run the code it does not specify a -1, 0 or 1.

Re: Do not send email if no data

sbb — Thu, 12 Feb 2015 14:53:03 GMT

Often SAS will set a useful _AUTOMATIC_ system macro variable which you can see with this command:

%PUT _AUTOMATIC_;

However, for some reason, a zero-record output from PROC EXPORT generates a &SYSNOBS value of 793 (on my SAS 9.4 TS1M1 Windows environment. Normally I would have expected a zero-value for this variable. Oh well.

An alternative is to add your DATA _NULL_; step with a SET that uses NOBS=OBSCOUNT parameter -- but you must have the IF stmt test before the SET, not after.

Then using this technique, you can conditionally invoke your "no data to report" macro using CALL EXECUTE on the IF statement.

Re: Do not send email if no data

Char — Thu, 12 Feb 2015 15:28:28 GMT

I think this works...

%macro check();

proc sql noprint;

select count(*) into: chk

from mydata;

quit;

%IF &CHK > 0 %THEN %DO;

proc export data=work.mydata

outfile='D:\folder\test\mydata.csv'

dbms = csv replace;

delimiter=';' ;

run;

filename mymail email "me.me@email.com"

subject="My Mail to You"

attach='D:\folder\test\mydata.csv';

data _null_;

file mymail;

run;

%END;

%mend check;

%check();

Re: Do not send email if no data

LAtwood — Thu, 12 Feb 2015 15:46:19 GMT

RW9 I receive the above error. Also, when I insert the single quote before filename it turns everything teal, not sure if that is what I'm supposed to do.

Re: Do not send email if no data

aci_kc — Thu, 12 Feb 2015 17:12:19 GMT

if you need to set the count macro var without actually reading the file, use the following.

data _null_;

call symput('count',numb_rows);

set work.mydata nobs=numb_rows;

stop;

run;

Re: Do not send email if no data

LAtwood — Thu, 12 Feb 2015 17:35:04 GMT

Thank you Char. That worked perfectly!

Re: Do not send email if no data

Char — Thu, 12 Feb 2015 17:46:36 GMT

No Problem. I'm glad it worked!

Re: Do not send email if no data

RW9 — Thu, 12 Feb 2015 18:00:38 GMT

Yes, you did not fully inspect my code. You need to put quotes around the call in the call execute. Call execute accepts a string and passes it out as text. So yes, it would change color.