Conditional E-mail Report

[gizeme]

I have a report with store sales information. This information is kept on a separate line for each store. I would like to send this information to the stores on a daily basis. but each store must send its own figures in the mail. how can I do that?

[Patrick]

@gizeme 

You create a report per store and then send this store specific report to the store specific email address.

First step: Write the program which creates the report and sends it via email for a single store. 

Then wrap a macro around this code and replace the relevant bits (like the email address) with macro variables which you can pass in as parameters to the macro.

Now create a data set with these parameters per store (1 row per store) and use a call execute where you call the macro and pass in the parameter values (1 call of the macro per store).

[gizeme]

its given an error

proc sql;
SELECT count(DISTINCT ID) into :numPeople
FROM MAGAZA_EXCEL;
%let numPeople = &numPeople;
quit;
proc sql;
SELECT DISTINCT ID
into :ID - :ID&numPeople, :mail - :mail&numPeople
FROM MAGAZA_EXCEL;
quit;

%macro send_report_emails();
%do i=1 %to &numPeople;
%let ID = &&ID&i;
%let to = &&mail&i;

options emailhost=("smtp.office365.com" port=xxx STARTTLS auth=login id="trreport@xxxx" pw="xxxxx") emailid="trreport@yrnet.com" emailpw="xxxxx";

filename mymail email "&to" SUBJECT = "Report Status" from="trreport@xxxx" content_type="application\xlsx" ;

data _null_;
put "The following reports are complete: &ca";
put "The following reports are complete: &debit";

run;
%end;

%mend send_report_emails;
%send_report_emails;