topic Re: Using if .. else.. to attach multiple files in email in SAS Procedures

Using if .. else.. to attach multiple files in email

aquabu — Tue, 08 Jul 2014 18:56:43 GMT

Hello everyone,

I would like to check the record length of a dataset and if the record length > 0 , then email the data of the dataset in an email. Any idea how I can achieve this?

specifically, i need to know how to use if..then...else to attach a particular file

Thank you for your help.

Regards

%let file1 = "C:\sasdata\Output\delta\

&RepDate.deltaProdFile.csv";

%let file2 = "C:\sasdata\Output\delta\

&RepDate.deltaStatusFile.csv";

%macro nobs(ds);

%let DSID=%sysfunc(OPEN(&ds.,IN));

%let NOBS=%sysfunc(ATTRN(&DSID,NOBS));

%let RC=%sysfunc(CLOSE(&DSID));

&NOBS

%mend;

%let total_rec1 = %nobs(WORK.DATASET1); /* This has been output to file1 */

%let total_rec2 = %nobs(WORK.DATASET2); /* This has been output to file2 */

/* Emailing the file */

filename mymail email

to = ('myemail@hostname.com' )

subject= "ATTACHMENT TEST"

attach= (&file1. &file2.)

;

data _null_;

file mymail;

put "Auto message generated from SAS - Do Not Reply...";

run;

Re: Using if .. else.. to attach multiple files in email

jwillis — Tue, 08 Jul 2014 19:27:17 GMT

This worked for me. You can adapt it to fit your needs. The code looks for an empty dataset. If the file is empty, an email is sent to the code submitter, and the processing stops. Otherwise, the code continues processing.

%macro mailter;
filename mymail email;

                    data _null_;
                      file mymail
                           subject=(" Table does not exist or has zero observations.   &sysdate. ")
                           TO=('email@address');
                      put "   The    Table OPS.SAS_NC does not exist or has zero observations.   &sysdate.         ";
                    run;

                   data _null_;
                       abort abend;
                   run;
%mend mailter;

%macro chekbetr(chekfil=ops.sas_nc);
   %if %sysfunc(exist(&chekfil.)) %then %do;
            /* PC SAS cannot read the metadata of the file in a remote server */
            /* If the file exists, create a temporary dataset that will contain at least one? observation */
            /* Then read the metadata NOBS number of the temporary dataset */
                   proc sql noprint;
                      drop table work.obscount;
                      create table work.obscount as
                     SELECT distinct a.recordid
                      FROM &chekfil. as a
                      ;
                     SELECT distinct a.nobs into :obscnt
                      FROM dictionary.tables as a
                      WHERE LIBNAME = "WORK" and memname = "OBSCOUNT"
                       ;
                  quit;
                  proc sql noprint; drop table work.obscount; quit;
          %if &obscnt. = 0 %then do;
               %mailter;
          %end;
%end;
%mend chekbtr;

Re: Using if .. else.. to attach multiple files in email

aquabu — Tue, 08 Jul 2014 19:45:37 GMT

Thanks for the code. It however does not solve my purpose since I want to be able to send one email with either 1 or 2 attachments depending on whether the datasets have any records.

Anyway I can do an if...then...else on the 'Attach' statement?

Re: Using if .. else.. to attach multiple files in email

jwillis — Wed, 09 Jul 2014 18:29:56 GMT

Hi aquabu,

You can create macros that queries for file records and one query that looks to see how many files to attach.

macro1;

does the first dataset exist and does it have records? create a macro variable that has true or false value.

mend;

macro2;

does the second dataset exist and does it have records? create a macro variable that has true or false value.

mend;

macro3;

%if macro1 = true and macro2 = true %then %do;

put mail code to send two attachments

%end;

%else %if macro1 = true and macro2 = false %then %do;

put mail code to send file1 only as attachment

%end;

%else %if macro1 = false and macro2 = true %then %do;

put mail code to send file2 only as attachment

%end;

%else %do;

put mail code to send an email with no attachments

%end;

mend;

Re: Using if .. else.. to attach multiple files in email

aquabu — Thu, 10 Jul 2014 16:44:33 GMT

Thanks jwillis.

Thats what I think I might have to do.

Regards