Do not send email if no data

[LAtwood]

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;

[PoornimaRavishankar]

%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);

[LAtwood]

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.

[PoornimaRavishankar]

%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);

[PoornimaRavishankar]

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.

[Ksharp]

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

[LAtwood]

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

[RW9]

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.

[LAtwood]

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.

[RW9]

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.

[sbb]

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.

[Char · Accepted solution]

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();      

[LAtwood]

Thank you Char.  That worked perfectly!

[Char]

No Problem.  I'm glad it worked!

[aci_kc]

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;