topic Re: How to send an email from an existing table in SAS Programming

How to send an email from an existing table

shlomiohana — Sat, 12 Feb 2022 19:18:22 GMT

Hello,

I have the following final table (every day the table contents change):

CARD Table:

The code:

%macro mymail(
  from=freetalk@gmail.com,
  to=,
  acct_no=,
  branch_id=,
  card=,
  id=
  );

  filename outbox email "freetalk@gmail.com" encoding="utf-8";

  data _null_;
    file outbox
    to=( "&to")
    from= ("&from")
    subject= "Closing &acct_no - &branch_id";
    put "Cancel any instructions in the account &acct_no - &branch_id :" ;
    put /;
    put " &card - &id";
    put /;
      
  run;
%mend;


data _null_;
  set CARD;
  length cmd $500;
  cmd=cats( '%mymail(to=',Mail_to,',acct_no=',Account_Number,',branch_id=',Branch_ID,',card=',Card_No,',id=',ID,')' );
  call execute(cmd);
run;

Sorry for the pictures, but the post will be deleted if not automatically.

I would appreciate your help.

How to send an email from an existing table

shlomiohana — Sat, 12 Feb 2022 18:04:39 GMT

Hello,

I have the following final table (every day the table contents change):

CARD Table:

The code:

%macro mymail(
  from=freetalk@gmail.com,
  to=,
  acct_no=,
  branch_id=,
  card=,
  id=
  );

  filename outbox email "freetalk@gmail.com" encoding="utf-8";

  data _null_;
    file outbox
    to=( "&to")
    from= ("&from")
    subject= "Closing &acct_no - &branch_id";
    put "Cancel any instructions in the account &acct_no - &branch_id :" ;
    put /;
    put " &card - &id";
    put /;
      
  run;
%mend;


data _null_;
  set CARD;
  length cmd $500;
  cmd=cats( '%mymail(to=',Mail_to,',acct_no=',Account_Number,',branch_id=',Branch_ID,',card=',Card_No,',id=',ID,')' );
  call execute(cmd);
run;

According to the code, they will send 5 emails, but I would like to change that they will only send 4 emails.
When the Branch_ID + Account_Number + ID + Mail_to fields are the same in the CARD table, it will send one email instead of two.

The email will look like this:

The line 2341245543 - 21312 was added to the existing email instead of sending 2 emails.

I would appreciate your help.

Re: How to send an email from an existing table

ErikLund_Jensen — Sat, 12 Feb 2022 22:42:05 GMT

Hi @shlomiohana

It seems that you can send code in a code box, so you could have added code to generate the input data (as expected in this forum). And the expected email as text or inside a comment in the code box as well. I know how to code it if you provide the input.

Re: How to send an email from an existing table

shlomiohana — Sun, 13 Feb 2022 07:59:38 GMT

hi, the input:

data card;

     infile datalines truncover dlm='|' dsd;

     input Branch_ID :20. Account_Number :20. ID :20. Card_No :$20. Mail_to :$20.;

     datalines;

1|1234567|21312|1231243423|abc@gmail.com

1|1234567|21312|2341245543|abc@gmail.com

12|2342344|23466|8979879879|rer@gmail.com

34|2344523|98986|3453263223|sdr@gmail.com

22|1235534|23214|2353662623|wwt@gmail.com
;

Re: How to send an email from an existing table

ballardw — Sun, 13 Feb 2022 08:18:37 GMT

Blank lines in Datalines are a bad idea. Is that actually the code?

When I run that I get this in the log:

156  data card;
157
158       infile datalines truncover dlm='|' dsd;
159
160       input Branch_ID :20. Account_Number :20. ID :20. Card_No :$20. Mail_to :$20.;
161
162       datalines;

NOTE: The data set WORK.CARD has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):

Which means there are 5 observations with all missing data and if something is supposed to be done with each observation then you have lots of blank observations to deal with.

@shlomiohana wrote:

hi, the input:

data card;

     infile datalines truncover dlm='|' dsd;

     input Branch_ID :20. Account_Number :20. ID :20. Card_No :$20. Mail_to :$20.;

     datalines;

1|1234567|21312|1231243423|abc@gmail.com

1|1234567|21312|2341245543|abc@gmail.com

12|2342344|23466|8979879879|rer@gmail.com

34|2344523|98986|3453263223|sdr@gmail.com

22|1235534|23214|2353662623|wwt@gmail.com
;

Re: How to send an email from an existing table

shlomiohana — Sun, 13 Feb 2022 08:41:49 GMT

Hi, this is the correct code: (no spaces)

data card;
     infile datalines truncover dlm='|' dsd;
     input Branch_ID :20. Account_Number :20. ID :20. Card_No :$20. Mail_to :$20.;
     datalines;
1|1234567|21312|1231243423|abc@gmail.com
1|1234567|21312|2341245543|abc@gmail.com
12|2342344|23466|8979879879|rer@gmail.com
34|2344523|98986|3453263223|sdr@gmail.com
22|1235534|23214|2353662623|wwt@gmail.com
;

Re: How to send an email from an existing table

ErikLund_Jensen — Sun, 13 Feb 2022 11:39:24 GMT

Hi @shlomiohana - thanks for the code.

It is not necessary to use a macro for this. It can all be done in one data step using first. and last. with by-variables to control the beginning of a new mail and sending it before the next mail starts. Remember to enable the File statement in the code when you try it.

I wrote a "real" program also with the usual bells and whistles, it is included as attachment . And I may have sent test mails to your receivers, I am not sure, so I hope they are fictive mail accounts.

data card;
     infile datalines truncover dlm='|' dsd;
     input Branch_ID :20. Account_Number :20. ID :20. Card_No :$20. Mail_to :$20.;
     datalines;
1|1234567|21312|1231243423|abc@gmail.com
1|1234567|21312|2341245543|abc@gmail.com
12|2342344|23466|8979879879|rer@gmail.com
34|2344523|98986|3453263223|sdr@gmail.com
22|1235534|23214|2353662623|wwt@gmail.com
;
run;
	
* Sort to make sure first. and last. will work as expected in data step;
proc sort data=&_INPUT;
	by Branch_ID Account_Number ID Mail_to Card_No;
run;

* Send emails;
%let mail_from = freetalk@gmail.com;
filename outbox email encoding="utf-8";
data _null;
	set card (obs=3);
	by Branch_ID Account_Number ID Mail_to;
	*file outbox;

	if first.Mail_to then do;
		account = catx(' ', Account_Number, '- 1');
		put '!em_subject! Closing ' account; 
		put "!em_from! &mail_from";
		put '!em_to! ' mail_to;

		put 'Cancel any instructions in account ' Account_Number / ;
	end;

	put card_no '-' ID;

	if last.Mail_to then do;
		put '!em_send!';       
		put '!em_newmsg!';     
		put '!em_abort!'; 
	end; 
run;

I hope you can use it, and don't hesitate to ask any further questions.

Re: How to send an email from an existing table

ErikLund_Jensen — Sun, 13 Feb 2022 11:44:44 GMT

Hi @shlomiohana

I used a real mail address in my full program to test it, and I forgot to change the address before I attached it.

Please remove it from any tests you make.

Re: How to send an email from an existing table

andreas_lds — Mon, 14 Feb 2022 06:47:50 GMT

I have merged your identical posts.

Re: How to send an email from an existing table

shlomiohana — Mon, 14 Feb 2022 17:16:02 GMT

@ErikLund_Jensen Thank you very much 🙂