topic Re: Send an email once even if no data exists but after that do not send an email if no data exists in New SAS User

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

sophie07 — Mon, 08 Apr 2019 10:37:57 GMT

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

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

andreas_lds — Mon, 08 Apr 2019 11:28:29 GMT

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.

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

sophie07 — Mon, 08 Apr 2019 12:07:41 GMT

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

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

sophie07 — Mon, 08 Apr 2019 12:11:25 GMT

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'

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

andreas_lds — Fri, 12 Apr 2019 05:19:01 GMT

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&docsetTarget=p096aldg7bq6qsn10m4044qbslka.htm

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.

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

sophie07 — Fri, 12 Apr 2019 07:06:32 GMT

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