Difference between SAS DIS and Batch

[susana_alves]

Hi everyone!

I need to send an email from sas using the code below:

PROC SQL;
SELECT email
INTO :email_address separated by '" "'
FROM WORK.EXAMPLE_TABLE
WHERE group='example';
QUIT;

DATA WORK.LIST_MAIL;
FORMAT emailaddr $50.;
INFORMAT emailaddr $50.;
INPUT emailaddr;
CARDS;
&email_address
;
RUN;

PROC SQL NOPRINT;
SELECT CAT('"',STRIP(emailaddr),'"') AS QtdEmail INTO :EmailAddrList Separated by ' '
FROM WORK.LIST_MAIL;
QUIT;

%put &EmailAddrList;

data _null_;
file sendit email
to=(&EmailAddrList)
subject="Example 1"
put "It's an example";
run;

When I run the code in SAS Data Integration Studio (DIS) the email is sent but when I run the code via batch the email isn't sent. On DIS, variable EmailAddrList is equal to "someone@example.com" and on batch variable EmailAddrList is equal "&email_address", so this is the reason why SAS can't sent the email via batch.

Does anyone know how I solve it?

Thank you!

[tsap]

Try updating your logic to look like this:

DATA WORK.Have;
FORMAT 		GROUP $1.	email $50.; 
INFORMAT	GROUP $1.	email $50.; 
INPUT  		GROUP		email;
CARDS;      
A AdamApple@gmail.com
A BillBobington@yahoo.com
B ChuckCooper@hotmail.com
;


PROC SQL;
SELECT 
CATS('"',email,'"') AS 	QtdEmail 	INTO :EmailAddrList Separated by ' '
FROM WORK.HAVE
WHERE group='A';
QUIT;
%PUT &=EmailAddrList.;


DATA _NULL_;
	file sendit email
	to		= (&EmailAddrList.)
	subject	= "Example 1"
	put "It's an example";
RUN;

To me it looks like you have excess logic that could potentially be causing some issues when running it.

See if the new logic provided above works for both SAS DIS and Batch.

[susana_alves]

Thanks for your help, but it did not work. The problem is that in the select statement there are no rows selected, but the table has data.

Here is the log:

PROC SQL;
2743 SELECT email
2744 INTO :email_address separated by '" "'
2745 FROM WORK.EXAMPLE_TABLE
2746 WHERE group='example';
NOTE: No rows were selected.
2747 QUIT;

When I run the job via SAS DIS, the select statement selects rows, but when I run the same code via batch, it selects nothing.

Do you have any idea why this happens and how do I solve it?

[tsap]

Are there any other errors/warnings/notes throughout the rest of the log when you run the code in Batch?

Are you running with these options set? options mlogic mprint symblogen

(to get the most information out of your log).

While the updated code is more efficient, the same issue appears to be present, which is why your original post stated you were getting a value of &email_address.

The initial query (when run in batch) was getting 0 rows. So the macro variable &email_address. that was supposed to be created, wasn't. Then in the next step that macro isn't able to resolve since it wasn't created (no rows to populate it), so a table is created with the literal text '&email_address.'.

Where for some reason, when run in DIS the initial query does get rows and does populate the &email_address. macro variable.

[tsap]

Try adding this to your query for running in batch mode:

options obs = max ;

PROC SQL;
OBS=MAX;
SELECT email
INTO :email_address separated by '" "'
FROM WORK.EXAMPLE_TABLE
WHERE group='example';
QUIT;

To see why I'm making this suggestion, check out this link: https://www.lexjansen.com/pharmasug/2010/CC/CC05.pdf

[susana_alves]

Thanks!

[tsap]

If the 'obs=max' post solved your issue, please mark that message as the solution. So that others know exactly message resolved your issue, if they encounter it as well.

Thanks