Sending an Email from SAS with Program if there is data in final table after a project is fully run

Question

I have not been able to get an email sent when my program is done running. I would like to send an email if there is a claim number that populates in the Match_Tracker table (this is the final output in the project). Claim_Number is the variable in the Match_Tracker table that I thought would work best. However, It would be better if the criteria was if any data populates in the table itself.

%let send_email = 0;

data _null_;
set gridwork.MATCH_TRACKER;
if Claim_Number > 0 then do;
call symput('send_email',1);
stop;
end;
run;


%macro send_email;
%if &send_email > 0 %then %do;
filename outbox email '[email protected]';
data _null_;
file outbox to=("[email protected]") subject = "Test";
put "There is a claim";
put " ";
run;
%end;
%mend;
%send_email;

Answer 1

Try changing your data step to this instead. It will count the number of observations in gridwork.MATCH_TRACKER where the claim_number > 0. It will return 0 if there are none, and the number of observations otherwise. If it's > 0, an email will be sent out.

%macro send_email;

    /* Open the dataset and count the number of observations with the where statement */
    %let dsid = %sysfunc(open(gridwork.MATCH_TRACKER(where=(Claim_Number > 0))));
    %let nobs = %sysfunc(attrn(&dsid, nlobsf));
    %let rc   = %sysfunc(close(&dsid));

    %if &nobs > 0 %then %do;
        filename outbox email '[email protected]';

        data _null_;
            file outbox to=("[email protected]") subject = "Test";
            put "There is a claim";
            put " ";
        run;
    %end;
%mend;
%send_email;