BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LAtwood
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Char
Obsidian | Level 7

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

14 REPLIES 14
PoornimaRavishankar
Quartz | Level 8

%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
Calcite | Level 5

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
Quartz | Level 8


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

PoornimaRavishankar
Quartz | Level 8


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
Super User

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
Calcite | Level 5

saslog.JPG

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Calcite | Level 5

Thank you Char.  That worked perfectly!

Char
Obsidian | Level 7

No Problem.  I'm glad it worked!

aci_kc
Fluorite | Level 6

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 14 replies
  • 6848 views
  • 3 likes
  • 7 in conversation