Send an email once even if no data exists but after that do not send an email if no data exists

[sophie07]

I'm not sure how to explain this. I'm currently running a script to see which items have updated in a report. There is a column named "updated" which is set to 'Yes' if it has updated and 'No' if its not. i am required to send an email out every day with a full report which i attach in an email using SAS. the script is kicked off every 30 minutes. What i'm trying to do is add a check to say if there are no records for updated = 'No' then no email should send out. the problem is i still need to send out an initial email once a day. 

this is what i'm using for my check:

i would like to schedule 

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

[andreas_lds]

The current proc sql checks for observations not regarding the value of "updated". The following step should fix that issue:

proc sql noprint;
  select count(*) into: chk trimmed
    from mydata
      where updated = 'YES'
  ;
quit

For the other issue (always send the first e-mail) you need something to identify the first run of the day. Maybe the time of day could be used. If can't identify the first run that way, you need to save a trigger outside the sas session.

[sophie07]

thank you...i was thinking about the time of day as well....will have a look at that.

[sophie07]

sorry im not that familiar with sas...how would i put the time of day as a check? lets say it needs to send out the full email at 8am, after that it should only send out an email if there are any updated = 'No'

[andreas_lds]

The datastep-function time() returns the current time as number of seconds since midnight, formats are used to display those values in an easy to read way. The documentation has examples: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarg...

Time-constants (8 am in your case) are written like this '08:00't. So in a data step you can do:

if time() = '08:00't then do;
  /* what ever is to be done */
end;

Unfortunately this will hardly ever work as expected, because it has to exactly 8 am - zero minutes, seconds and even milliseconds - and it is hardly possible to schedule a job, so that it execute that piece of code exatly at that time. But as you said: "the script is kicked off every 30 minutes.", the following code should solve the issue:

%local firstRun;

data _null_;
   t = time();

   if '08:00't <= t < '08:30't then do;
      call symputx('firstRun', 'yes');
   end;
   else do;
      call symputx('firstRun', 'no');
   end;
run;


%local chk;
/* add code setting the variable chk here */


/* no quotes in macro code! */
%if &chk. > 0 %then %do;
   %put NOTE: Sending normal mail;
   /* normal send code */
%end;
%else %do;
   %if &firstRun. = yes %then %do;
     /* code to send the first mail, even if nothing was updated */
   %put NOTE: Sending first mail without updates;
   %end;
%end;

One final note: starting with macro-programming without being familiar with the fundamental concepts of the sas language seems to be not the best basis for solving problems.

[sophie07]

Thank you so much!

yes i know, unfortunately this was handed down to me from a predecessor and i am just trying to get it to work as expected. 

i appreciate all the help