DATA Step, Macro, Functions and more

Do not send email if no data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 83
Accepted Solution

Do not send email if no data

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;


Accepted Solutions
Solution
‎02-12-2015 10:28 AM
Occasional Contributor
Posts: 12

Re: Do not send email if no data

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

View solution in original post


All Replies
Contributor
Posts: 53

Re: Do not send email if no data

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

Frequent Contributor
Posts: 83

Re: Do not send email if no data

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.

Contributor
Posts: 53

Re: Do not send email if no data


Smiley Happy

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

Contributor
Posts: 53

Re: Do not send email if no data


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.

Super User
Posts: 9,682

Re: Do not send email if no data

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

Frequent Contributor
Posts: 83

Re: Do not send email if no data

saslog.JPG

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

Super User
Super User
Posts: 7,407

Re: Do not send email if no data

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.

Frequent Contributor
Posts: 83

Re: Do not send email if no data

saslog.JPG

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.

Super User
Super User
Posts: 7,407

Re: Do not send email if no data

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.

Super Contributor
Super Contributor
Posts: 3,174

Re: Do not send email if no data

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.

Solution
‎02-12-2015 10:28 AM
Occasional Contributor
Posts: 12

Re: Do not send email if no data

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

Frequent Contributor
Posts: 83

Re: Do not send email if no data

Thank you Char.  That worked perfectly!

Occasional Contributor
Posts: 12

Re: Do not send email if no data

No Problem.  I'm glad it worked!

Occasional Contributor
Posts: 6

Re: Do not send email if no data

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 924 views
  • 1 like
  • 7 in conversation