Need Help! Need to send excel attachments dynamically to respected email

[Bhargavi221]

Data demo;
input part $ manager $10. manager_email $33.;
cards;
1055014 ravi ravi@gmail.com
1472388 ravi ravi@gmail.com
1515335 ravi ravi@gmail.com
1606838 ravi ravi@gmail.com
1788165 hari hari@gmail.com
2093853 hari hari@gmail.com
20R7177 hari hari@gmail.com
20R7235 ramya ramya@gmail.com
20R7509 bhargavi bhargavi@gmail.com
20R8560 bhargavi bhargavi@gmail.com
;
run;

i need to send excel attachments to the list of parts associated to the managers dynamically to respected email.

Example :

Partnos 1055014 1472388 1515335 1606838  excel has to send to ravi@gmail.com 

Partnos 1788165 2093853 20R7177 excel has to send to hari@gmail.com 

Partnos 20R7235 excel has to send to ramya@gmail.com 

Partnos 20R7509 20R8560  excel has to send to bhargavi@gmail.com 

[SuryaKiran]

Hi,

You can use call symput() to send email based on the values in a dataset. 

[Bhargavi221]

@SuryaKiran Thanks for your replay.

I know i need to use call symput but unfortunately it did not worked for me, can you give me detail explanation.

Thanks in advance!

[Shmuel]

Based on @SuryaKiran suggestion and assuming data is sorted by email then

data _null_;
   retain no_of_emails 0;
   set demo;
    by manager manager_email;   /* in case two managers have same name */
         length answer $80;   /* adapt to max length expected */

         if first.manager_email then answer = part;
         else answer = catx(' ',answer,part);


         if last.manager_email then do;
            call symput('PARTS',trim(answer));
            call execute(<send letter code using &parts or answer>);
        end;
run;

        

[Bhargavi221]

@Shmuel

Okay, But how can i send list of parts associated with the respected mangers as a excel attachement dynamically.

[Shmuel]

Can you post your code assuming there is only one email ?

Then I or someone in the forum will show how to merge it in the suggested code.

[SuryaKiran]

Hi @Shmuel,

You cannot use a macro variable reference to retrieve the value of a macro variable in the same program (or step) in which SYMPUT creates that macro variable and assigns it a value.

            call symput('PARTS',trim(answer));
            call execute(<send letter code using &parts or answer>);

[Shmuel]

@SuryaKiran, you are absolutely right.

The call execute bewildered me to think it is executing after the step ends.

Thank you to remind me.

[SuryaKiran]

What files do you want to attach in the email? Does the attachment files depend on the 'part' numbers for each manager. Then create a dataset that includes the physical path for the attachments and create a macro for sending an email with attachments.

Something like this (untested):

Data demo;
input part $ manager :$10. manager_email :$33. attachment :$50.;
cards;
1055014 ravi ravi@gmail.com test1.xlsx
1472388 ravi ravi@gmail.com test2.xlsx
1515335 ravi ravi@gmail.com test3.xlsx
1606838 ravi ravi@gmail.com test4.xlsx
1788165 hari hari@gmail.com test5.xlsx
2093853 hari hari@gmail.com test6.xlsx
20R7177 hari hari@gmail.com test7.xlsx
20R7235 ramya ramya@gmail.com test8.xlsx
20R7509 bhargavi bhargavi@gmail.com test9.xlsx
20R8560 bhargavi bhargavi@gmail.com test10.xlsx
;
run;
proc sort data=demo;
by manager manager_email;
run;


%Macro email_out(to=,subject=,attach=);
filename mymail email 
	to=&to.
   	subject="Attachemnts for parts &subject."
   	attach=(&attach.);

data _null_;
   file mymail;
   put 'Hi,';
   put 'This is my SAS email file.';
run;

%mend email_out;


data _null_;
format partnos attach $50.;
do until(last.manager);
set demo;
by manager manager_email;
attach=catx(" ",attach,quote(strip(attachment)));
partnos=catx(' ',partnos,part);
end;
call execute(cats("%email_out(to=",quote(strip(manager_email)),",subject=",partnos,"attach=",attach,")"));
run;

[Bhargavi221]

@SuryaKiran The code Logic looks fine. But its throwing error even though declaration of positional parameters are fine.

SAS Log:

25 GOPTIONS ACCESSIBLE;
26 data _null_;
27 format partnos attach $50.;
28 do until(last.manager);
29 set demo;
30 by manager manager_email;
31 attach=catx(" ",attach,quote(strip(attachment)));
32 partnos=catx(' ',partnos,part);
33 end;
34 call execute(cats("%email_out(to=",quote(strip(manager_email)),",subject=",partnos,"attach=",attach,")"));
ERROR: All positional parameters must precede keyword parameters.
34 call execute(cats("%email_out(to=",quote(strip(manager_email)),",subject=",partnos,"attach=",attach,")"));
___________
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.

34 call execute(cats("%email_out(to=",quote(strip(manager_email)),",subject=",partnos,"attach=",attach,")"));
______
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

35 run;
36
37 GOPTIONS NOACCESSIBLE;
38 %LET _CLIENTTASKLABEL=;
39 %LET _CLIENTPROCESSFLOWNAME=;
40 %LET _CLIENTPROJECTPATH=;
41 %LET _CLIENTPROJECTPATHHOST=;
42 %LET _CLIENTPROJECTNAME=;
43 %LET _SASPROGRAMFILE=;
44 %LET _SASPROGRAMFILEHOST=;
2 The SAS System 03:02 Tuesday, November 27, 2018

______________________________
22
200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

45
46 ;*';*";*/;quit;run;
____
180

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

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

47 ODS _ALL_ CLOSE;
48
49
50 QUIT; RUN;
51

[SuryaKiran]

This should work:

data _null_;
format partnos attach $1000.;
do until(last.manager);
set demo;
by manager manager_email;
attach=catx(" ",attach,quote(strip(attachment)));
partnos=catx(' ',partnos,part);
end;
call execute(cat('%email_out(to=',quote(strip(manager_email)),', subject=',strip(partnos),', attach=',strip(attach),')'));
run;

Make sure the value inside the CALL EXECUTE is resolved as required.