topic Re: Pulling email values in a keyword parameter for an email macro in SAS Programming

Pulling email values in a keyword parameter for an email macro

jeboltz — Mon, 09 Jan 2023 20:47:11 GMT

Hello!

I’ve been trying everything to pull in the values of an email list from a dataset in an email macro and it just keeps coming through as missing. Any suggestions on how to fix this? Code is as follows:

%MACRO EMAIL(orgid=, PSName=, PSEmail=, uid=);
Filename LIST Email
From=“test@test.com”
Sender=“Team”
To=“&PSEmail”
Subject=“please read”

Data _null_;
(Code continues)…

Re: Pulling email values in a keyword parameter for an email macro

SASKiwi — Mon, 09 Jan 2023 20:54:45 GMT

Your FILENAME statement is missing a semicolon on the end. If you are still having problems then post the complete SAS log including notes and errors.

Re: Pulling email values in a keyword parameter for an email macro

jeboltz — Mon, 09 Jan 2023 21:28:16 GMT

Thank you for the quick reply! didn't fix it; here is the log:

1 + filename LIST Email; from="test@test.com" sender="Team" to=(".") subject="Alert";
----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

thank you!

Re: Pulling email values in a keyword parameter for an email macro

SASKiwi — Mon, 09 Jan 2023 21:55:03 GMT

Looks like SAS is being impacted by previous errors. Start a new SAS session and try your corrected program again.

Re: Pulling email values in a keyword parameter for an email macro

jeboltz — Mon, 09 Jan 2023 23:01:37 GMT

restarted; still pulling in the email as "."

the error it's throwing is "

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

and this seems to be around the to= part of the email designation.

Re: Pulling email values in a keyword parameter for an email macro

SASKiwi — Mon, 09 Jan 2023 23:16:31 GMT

Please post your complete SAS log then as requested.

Re: Pulling email values in a keyword parameter for an email macro

Reeza — Mon, 09 Jan 2023 23:25:10 GMT

Semicolon goes at the end of the subject, you have it too early now.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-send-email-using-SAS/ta-p/746523

@jeboltz wrote:

Thank you for the quick reply! didn't fix it; here is the log:

1 + filename LIST Email; from="test@test.com" sender="Team" to=(".") subject="Alert";
----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

thank you!

Re: Pulling email values in a keyword parameter for an email macro

Reeza — Mon, 09 Jan 2023 23:34:51 GMT

Before you make your code macro, do you have a working program that works and is tested? That should always be your first step before converting it to a macro.
If you do, comparing the two to figure out where they differ is a good start.

Re: Pulling email values in a keyword parameter for an email macro

Tom — Tue, 10 Jan 2023 04:43:44 GMT

Why did you insert a semicolon into the MIDDLE of the FILENAME statement?

You place semicolons at the end of the STATEMENT not at the end of lines of code.

filename LIST Email  from="test@test.com" sender="Team" to=(".") subject="Alert";

But why do you have a TO address that is just a period? That does not look right. I doubt that most sendmail routines would allow that type of email address.

Re: Pulling email values in a keyword parameter for an email macro

jeboltz — Tue, 10 Jan 2023 15:44:13 GMT

Hello,

I was able to get the macro to work by hard coding an email in--it's the dynamic variable looking for values that keeps coming through missing; I have two other keyword variables that do work, so I suspect it's something with the email structure itself, though I'm not sure if it's in the macro code or if it's how I save the email (as text, in an excel; please let me know if this is the problem).

here is the log from the latest attempt, I did change emails and text for privacy:

1 + filename LIST Email; to=(".") from=("test@test.com") sender=(" Team") subject="Alert " type='text/html' attach=

180

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

2 +("\\...Report.pdf");

2 + data _null_; file LIST; put '<html><head>'; put '<style type="text/css"

3 + MEDIA=screen><!--'; put 'body { font-family: Calibri; font-size: 12pt; }'; put '.Message1 { color: Black; font-size: 11pt; font-weight: bold; }'; put '.Message2 { color: Black; font-size: 13pt; font-weight: italic; }'; put

4 + '--></style></head><body>'; put "Hello Sarah Smith at 555, <br />"; put "<br />"; put "<i> text"; put "text

5 + text"; put "text"; put "text

6 + text"; put "text"; put "text </i>"; put "</body></html>"; run;

NOTE: The file LIST is:

E-Mail Access Device

ERROR: No TO= address specified for email.

NOTE: 51 records were written to the file LIST.

The minimum record length was 6.

The maximum record length was 109.

NOTE: DATA statement used (Total process time):

real time 0.53 seconds

cpu time 0.46 seconds

Re: Pulling email values in a keyword parameter for an email macro

Tom — Tue, 10 Jan 2023 15:53:39 GMT

Remove the spurious semicolon from the middle of the FILENAME statement.

You are currently trying to run these two statements:

filename LIST Email;    
to=(".") from=("test@test.com") sender=(" Team") subject="Alert " type='text/html' attach=("\\...Report.pdf");

SAS is properly flagging the second one as invalid. It sort of looks like an assignment statement, but you cannot use an assignment statement outside of a data step.

And the FILENAME statement is saying you want to use the EMAIL engine, but it is not setting any of the options like the TO address that the second error is complaining about.

Re: Pulling email values in a keyword parameter for an email macro

Tom — Tue, 10 Jan 2023 16:00:56 GMT

@jeboltz wrote:
Hello!

I’ve been trying everything to pull in the values of an email list from a dataset in an email macro and it just keeps coming through as missing. Any suggestions on how to fix this? Code is as follows:

%MACRO EMAIL(orgid=, PSName=, PSEmail=, uid=);
Filename LIST Email
From=“test@test.com”
Sender=“Team”
To=“&PSEmail”
Subject=“please read”

Data _null_;
(Code continues)…

So your FILENAME statement does not end in a semicolon. There is no DATA option on a FILENAME statement.

Use normal quote characters not the word processing stupid quotes.

%MACRO EMAIL(orgid=, PSName=, PSEmail=, uid=);
filename LIST Email
 from="test@test.com"
 sender="Team"
 to="&PSEmail"
 subject="please read"
;

data _null_;
....
run;
...
%mend email;

If you are getting errors when calling this macro we need to also see the code you used to call the macro to see if the values passed for the parameters makes any sense.

It would probably help to put the quotes around the email address into the macro call instead of in the macro code.

%MACRO EMAIL(orgid=, PSName=, PSEmail=, uid=);
filename LIST Email  from="test@test.com"  sender="Team" to=&PSEmail.
 subject="please read"
;
...
%mend email;
%email(PSEmail="someone@somewhere.com")

Re: Pulling email values in a keyword parameter for an email macro

jeboltz — Sun, 15 Jan 2023 22:10:25 GMT

Okay finally solved it. The excel had the email saved in an unacceptable format; I had to re-run and prepare the excel file used for the dataset pulled into the mail merge. Thank you for all your help!