topic Re: re: email attachments in SAS Programming

re: email attachments

twildone — Wed, 17 Jun 2015 14:09:40 GMT

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;

Re: re: email attachments

sbb — Wed, 17 Jun 2015 14:16:49 GMT

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

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

Re: re: email attachments

Astounding — Wed, 17 Jun 2015 14:25:04 GMT

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

Re: re: email attachments

twildone — Wed, 17 Jun 2015 14:45:06 GMT

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.

Re: re: email attachments

Astounding — Wed, 17 Jun 2015 15:22:22 GMT

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.

Re: re: email attachments

sbb — Wed, 17 Jun 2015 15:46:10 GMT

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.

Re: re: email attachments

twildone — Wed, 17 Jun 2015 16:33:52 GMT

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.

Re: re: email attachments

twildone — Wed, 17 Jun 2015 16:37:24 GMT

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.

Re: re: email attachments

Astounding — Wed, 17 Jun 2015 17:00:43 GMT

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.

Re: re: email attachments

sbb — Wed, 17 Jun 2015 20:27:08 GMT

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.

Re: re: email attachments

twildone — Thu, 18 Jun 2015 14:26:41 GMT

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;

Re: re: email attachments

twildone — Mon, 22 Jun 2015 14:39:49 GMT

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.

Re: re: email attachments

CTorres — Mon, 22 Jun 2015 16:27:24 GMT

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

Re: re: email attachments

twildone — Mon, 22 Jun 2015 17:57:53 GMT

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"

___

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

___

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.

Re: re: email attachments

CTorres — Mon, 22 Jun 2015 18:47:55 GMT

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

Re: re: email attachments

Tom — Mon, 22 Jun 2015 19:13:34 GMT

Why are you using macro code for this? The SQL step can generate the list of attachments.

LIBNAME project "U:\Data Programs";

PROC SQL noprint;

SELECT DISTINCT quote(catx('\',pathname('project'),cats(FILENAME1,'.PDF')))

INTO :FILELIST SEPARATED BY ' '

FROM WORK.FILE_LIST

;

QUIT;

FILENAME mail EMAIL

TO = "Lisa1245@yahoo.com"

FROM = "argo34@yahoo.com"

ATTACH=(&filelist)

;

data _null_;

file mail;

PUT "Hi Lisa,";

PUT " ";

PUT "This is test";

PUT " ";

PUT "Thanks,"

RUN;

Re: re: email attachments

twildone — Mon, 22 Jun 2015 19:46:49 GMT

Hi Tom....I made the changes as you suggested and I still get the following error message ( when I look at the SYMBOLGEN in the log, it indicates that the Macro variable Filelist resolves to exactly the name of the files that should be attached):

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.

Re: re: email attachments

Tom — Mon, 22 Jun 2015 20:11:50 GMT

Are you sure the files exist?

data _null_;

set file_list;

fname = catx('\',pathname('project',cats(filename1,'.PDF'));

exist = fileexist(fname);

put exist= fname= ;

run;

See if you can open the files from a data step.

FILENAME files (&filelist);

data _null_;

infile files recfm=n;

input x $char5. ;

put x= $hex10.;

stop;

run;

Re: re: email attachments

twildone — Tue, 23 Jun 2015 13:13:18 GMT

Hi Tom.....Yes the files exist. I even open them to make sure they both contained content. I ran the code you provided and it seems to indicate that the files exiist...Here is the output:

FILENAME1

Sales Report

Return Merchandise Report

Re: re: email attachments

Kurt_Bremser — Tue, 23 Jun 2015 14:30:44 GMT

The (not so) funny thing is that the SAS knowledge base has no fitting entry for

ERROR: Unable to access E-mail attachments.

google also does not find a real SAS documentation for it.

So this very much looks like an undocumented message.

I'd throw it to SAS TS.