DATA Step, Macro, Functions and more

re: email attachments

Reply
Regular Contributor
Posts: 229

re: email attachments

Hi, I am trying to send an e-mail through SAS which can have more than one attachment. My approach was to create a list of the names of the files and then do a loop in the attachments. The files are stored on the U drive. I just can't seem to get the loop to work and wondering if I would need to use a Call Executive() statement. Any help would be greatly appreciated. Thanks,

LIBNAME project "U:\Data Programs";

PROC SQL;

     SELECT DISTINCT FILENAME1  

           INTO :FILENAMELIST1 SEPARATED BY '|'

     FROM WORK.FILE_LIST;

QUIT;

%macro send_email;

%DO i=1 %TO %SYSFUNC(COUNTW(&FILENAMELIST1));

%LET NEXT_FILENAMELIST1 = SCAN(&FILENAMELIST1,&i,|);

%PUT NEXT_FILE = &NEXT_FILENAMELIST1;

FILENAME mail EMAIL;

DATA _NULL_;

FILE mail

TO = "Lisa1245@yahoo.com"

FROM = "argo34@yahoo.com"

ATTACH=("%sysfunc(pathname(project))\&NEXT_FILE..PDF");

%END;

PUT "Hi Lisa,";

PUT " ";

PUT "This is test";

PUT " ";

PUT "Thanks,"

RUN;

%END;

%mend;

%send_email;

Super Contributor
Super Contributor
Posts: 3,174

Re: re: email attachments

Was it necessary to re-post the same question as was previously posted ?

https://communities.sas.com/thread/78123re: email with multiple attachments

Super User
Posts: 5,509

Re: re: email attachments

A starting point:  Switch from SCAN to %SCAN.  Then see what problems remain.

Regular Contributor
Posts: 229

Re: re: email attachments

Hi made the switch from SCAN to %SCAN and get the following:

 

WARNING: Apparent symbolic reference NEXT_FILE not resolved.

 

ERROR: Unable to access E-mail attachments.

Super User
Posts: 5,509

Re: re: email attachments

Are you interested in learning to write a macro?  Or are you interested in getting the program working whether or not you understand it?  This should be an easy issue for you to fix yourself.

Your program refers to &NEXT_FILE.  But it never creates &NEXT_FILE.

Try changing &NEXT_FILE. to &NEXT_FILENAMELIST1. and see if that takes care of it.  Keep both of the periods before "PDF".

Good luck.

Regular Contributor
Posts: 229

Re: re: email attachments

Posted in reply to Astounding

Hi Astounding.....I did make the change from &NEXT_FILE. to &NEXT_FILENAMELIST1. and still get the same error message  of Unable to access E-mail attachements. But from the log, for the ATTACH= , the location (my u: drive) with the filename are listed and correct me if I am wrong, I was expecting to see only the file name regardless where the file is stored.

Super User
Posts: 5,509

Re: re: email attachments

Now you're getting into an area where I can't help.  It looks like the earlier suggestions cleared up the macro language errors.  But I'm not familiar with the right syntax for sending emails.  Sorry, but perhaps there are others who can step in at this point.

Regular Contributor
Posts: 229

Re: re: email attachments

Posted in reply to Astounding

Hi...I made the changes as suggested and seems to work partly as it only attaches the very first one on the list. When I check the log, everything seem fine as  Symbolgen indicates thatthe macro variable I has resolve to 1, 2 and 3 (there are 3 files). Macrogen (Send_Mail)  for Attach= seems to be correct with the 3 different file names. But at least its accessing the file from the stored location which is good sign.

FILENAME mail EMAIL;

DATA _NULL_;

FILE mail

TO = "Lisa1245@yahoo.com"

FROM = "argo34@yahoo.com"

%macro send_email;

%DO i=1 %TO %SYSFUNC(COUNTW(&FILENAMELIST1,’|’));

%LET NEXT_FILENAMELIST1 = %SCAN(&FILENAMELIST1,&i,|);

ATTACH=("%sysfunc(pathname(project))\&NEXT_FILE NAMELIST1..PDF");

%END;

PUT "Hi Lisa,";

PUT " ";

PUT "This is test";

PUT " ";

PUT "Thanks,"

RUN;

QUIT;

%mend;

%send_email;

Super Contributor
Super Contributor
Posts: 3,174

Re: re: email attachments

Consider that the SAS code is executed either locally or remotely -- and the ATTACH=(...) is not resolved until execution time, so those files must be accessible at SAS-execution time on the specific system/machine were SAS is invoked.

Scott Barry

SBBWorks, Inc.

Super Contributor
Super Contributor
Posts: 3,174

Re: re: email attachments

When you execute your macro with the increased-diagnostics OPTIONS statement suggested in your other post, it will become clear that the macro invocation is going to generate a separate DATA step execution -- and even if it works correctly, that will in turn generate one EMAIL per attachment -- but your intended EMAIL BODY will not get generated as you have coded it.

Instead, possibly your %DO / %END loop is more appropriate for generating the data-string that encompasses the ATTACH=(.....)  and no more.

Again, suggest/recommend serious self-initiated desk-checking which will help reveal what is actually happening here -- multiple DATA step executions, one per item.

Also, you have too many %END; statements and I believe that your %PUT is intended to be a %LET, instead, as was mentioned.

Scott Barry

SBBWorks, Inc.

Regular Contributor
Posts: 229

Re: re: email attachments

Hi sbb....thank you for the suggestion of the OPTIONS statements...that is very helpful and I can see what happening. I did make the change of encompassing  the ATTACH=(.....)  with %DO/%END LOOP and that makes alot of sense.

Regular Contributor
Posts: 229

Re: re: email attachments

Hi....I am still having problems with the macro in attaching files to the email. The first file is attached and email is sent out with only the first attachment. Any suggestions why the error message...thanks.

MLOGIC(SEND_EMAIL):  Beginning execution.

SYMBOLGEN:  Macro variable FILENAMELIST1 resolves to Sales Report|Return Merchandise Report

MLOGIC(SEND_EMAIL):  %DO loop beginning; index variable I; start value is 1; stop value

      is 2; by value is 1. 

MLOGIC(SEND_EMAIL):  %LET (variable name is NEXT_FILENAMELIST1)

SYMBOLGEN:  Macro variable FILENAMELIST1 resolves to Sales Report|Return Merchandise Report

SYMBOLGEN:  Macro variable I resolves to 1

MLOGIC(SEND_EMAIL):  %PUT NEXT_FILENAMELIST1

NEXT_FILENAMELIST1

SYMBOLGEN:  Macro variable NEXT_FILENAMELIST1 resolves to Sales Report

MACROGEN(SEND_EMAIL):   ATTACH=("U:\Sales Data Programs\Sales Report.PDF");

MLOGIC(SEND_EMAIL):  %DO loop index variable I is now 2; loop will iterate again.

MLOGIC(SEND_EMAIL):  %LET (variable name is NEXT_FILENAMELIST1)

SYMBOLGEN:  Macro variable FILENAMELIST1 resolves to Sales Report|Return Merchandise Report

SYMBOLGEN:  Macro variable I resolves to 2

MLOGIC(SEND_EMAIL):  %PUT NEXT_FILENAMELIST1

NEXT_FILENAMELIST1

NOTE: Line generated by the invoked macro "SEND_EMAIL".

159         ATTACH=("%SYSFUNC(PATHNAME(PROJECT))\&NEXT_FILENAMELIST1..PDF");

            ______

            180

SYMBOLGEN:  Macro variable NEXT_FILENAMELIST1 resolves to Return Merchandise Report

MACROGEN(SEND_EMAIL):   ATTACH=("U:\Sales Data Programs\ Return Merchandise Report;

ERROR 180-322: Statement is not valid or it is used out of proper order.

Regular Contributor
Posts: 180

Re: re: email attachments

I think the problem here is that you can have only one ATTACH= option in the FILE statement. So you have to use the loop to generate the list of files to attach and the use only one attach= option. Something like:  (untested and may be more complex than that)

FILENAME mail EMAIL;

DATA _NULL_;

FILE mail

TO = "Lisa1245@yahoo.com"

FROM = "argo34@yahoo.com"

%macro send_email;

%LET mylist=;

%DO i=1 %TO %SYSFUNC(COUNTW(&FILENAMELIST1,’|’));

%LET NEXT_FILENAMELIST1 = %SCAN(&FILENAMELIST1,&i,|);

   &LET mylist=&mylist. "%sysfunc(pathname(project))\&NEXT_FILE NAMELIST1..PDF";   /* here you build the list */

%END;

ATTACH=("&mylist");

PUT "Hi Lisa,";

PUT " ";

PUT "This is test";

PUT " ";

PUT "Thanks,"

RUN;

QUIT;

%mend;

CTorres

Regular Contributor
Posts: 229

Re: re: email attachments

Hi CTorres.....Thank you so much for your suggestion. I tried your suggestion and got yhr following error message:

'

NOTE: Line generated by the macro variable "MYLIST".

162        ""U:\Sales Data Programs\Sales Report.PDF" 

           __

___                                                                                     

___                                                                                     

           49

49          

49          

           24

162      "U:\Sales Data Programs\Return Merchandise Report.PDF"

           ___

           49

           ___

           49

                                     The SAS System

MACROGEN(SEND_EMAIL):   QUIT;

MLOGIC(SEND_EMAIL):  Ending execution.

NOTE 49-169: The meaning of an identifier after a quoted string may change in a future

             SAS release.  Inserting white space between a quoted string and the

             succeeding identifier is recommended.

ERROR 24-2: Invalid value for the ATTACH option.

I re-ran it again but this time I removed the double quotes in the ATTACH=(&MYLIST)  and I get the following eror message:

NOTE: The file MAIL is:

      E-Mail Access Device

ERROR: Unable to access E-mail attachments.

NOTE: The SAS System stopped processing this step because of errors.

     

Thanks.

Regular Contributor
Posts: 180

Re: re: email attachments

Well, I am not very good using macro language specially when I have to deal with quotes and special characters like in this case.

If I were you I would avoid using macros because in my opinion SAS Base language is a much better tool to do what you need. I would:

1. Run SAS code (without macros) to test the email with one attachment.

2. Run SAS code to test the email with two or more attachments.

3. Once I am sure about the syntax then I would use the SAS Data Step to generate the list of n files using SAS Base (no macro at all).

I believe this is a safer approach than using macros.

Good luck

CTorres

Ask a Question
Discussion stats
  • 19 replies
  • 739 views
  • 0 likes
  • 6 in conversation