Users log calls using SQL database and the sample table is as below:
data Customers;
infile datalines;
input customer : $20. start_date : $7. end_date : $7. sampling_point: $35. name1 : $14. Action : $15. Customer_email : $30.;
datalines;
Tshepo 17feb19 21mar20 "d-pelin","d-roode","m-vaalkop" "tc","Mn" complete mmohotsi@gmail.com
Mighty 21Apr20 30Apr20 "d-air_res","d-deel" "hpc","Turb" incomplete mighty@gmail.co.com
Tumisi 17Apr19 26Apr20 "d-air_res","d-sasol" "Mn","Colour" incomplete Tumisi@gmail.com
;
run;
data want;
set Customers;
where action = 'incomplete';
run;
I was also able to extract the requested data from the database using the code below:
filename torun temp;
data _null_;
file torun;
set want;
put "****** QUERY FOR CUSTOMER : " customer " ******;";
put "proc sql; " / "create table " customer " as" / " select sample.sample_number,sample.sampling_point,sample.sampled_date,result.name,
result.numeric_entry,result.status, result.reportable";
put " from vgsql1.sample, vgsql1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between ";
put "'" start_date +(-1) "'d and" ;
put "'" end_date "'d" ;
put "and sample.sampling_point in (" sampling_point") and " /
" result.name in (" name1") and " ;
put"result.status = 'A';" / " quit;";
run;
%include torun;
A macro was then used to generate a report based on the information on the data using the code below:
The two reports named Mighty and Tumisi are attached
%macro poc_;
ODS _ALL_ CLOSE;
ods pdf file="&_POC_\Customer_Report&Today&_pdf_";
ODS ESCAPECHAR = '~';
proc sql NOPRINT;
select count(name) into: emailed
from MIGHTY;
quit;
%If &emailed >=0 %then %do;
Data tosend;
text = "There are no results for the information provided from date_started to date_ended";
run;
Proc report data = tosend;
COLUMN text;
define text / '';
title1 j=c Color = biv bold italic height=4.5
"~{style[preimage='\\him\DataStore\Internal\HIM_Water_Quality_Administration\SASImplementation\RW_Logo\LogoRW_29May2016.bmp'] } " ;
title2 j=c "~{style[vjust=m]Customer report}";
title4 j=C Color = biv "Report generated on: %sysfunc(today(), weekdate.) at %sysfunc(time(), timeAMPM8.)";
footnote1 'Information management Report Compiled by Water Quality Statistician Scientific Services' ;
run;
%END;
%ELSE
%DO;
Proc report data = MIGHTY;
run;
%END;
ODS _ALL_ CLOSE;
QUIT;
%mend poc_;
%poc_;
The report was then send to the customer using the code below:
%let _POC_ = C:\Users\mmohotsi\Documents\P_O_C\Reports;
%let _pdf_ = .pdf;
%let today = %sysfunc(today(), date9.);
options emailsys = SMTP;
options emailhost = email_host;
options emailauthprotocol = NONE;
filename Outbox email;
Data _null_;
file Outbox
to = ("mmohotsi@randwater.co.za" )
from = "mmohotsi@randwater.co.za"
Subject = "POC Prototype report"
attach ="&_POC_\Customer_Report&Today&_pdf_";
put 'Good day ';
put ' ';
put 'Kindly find the attached report for your attention';
put ' ';
put 'For any queries kindly contact Mighty Mohotsi on mmohotsi@randwater.co.za';
put ' ';
put 'Warmest regards';
put ' ';
Put 'Mighty';
run;
I need to find how do I automate this process such that each report is emailed to the intended recipient as provided for in the "Customers" data
Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md
%macro send_email;
%let _POC_ = C:\Users\mmohotsi\Documents\P_O_C\Reports;
%let _pdf_ = .pdf;
%let today = %sysfunc(today(), date9.);
options emailsys = SMTP;
options emailhost = email_host;
options emailauthprotocol = NONE;
proc sql;
select distinct Customer_email into: email_list separated by ',' from Customers
where action = 'complete';
quit;
%do i = 1 %to %sysfunc(countw("&emaillist",','));
%let email=%scan(&emaillist,&i.,%str(,));
filename Outbox email;
Data _null_;
file Outbox
to = ("&email." )
from = "mmohotsi@randwater.co.za"
Subject = "POC Prototype report"
attach ="&_POC_\Customer_Report&Today&_pdf_";
put 'Good day ';
put ' ';
put 'Kindly find the attached report for your attention';
put ' ';
put 'For any queries kindly contact Mighty Mohotsi on mmohotsi@randwater.co.za';
put ' ';
put 'Warmest regards';
put ' ';
Put 'Mighty';
run;
%end;
%mend send_email;
The programme works but the challenge is attaching the report that corresponds to the right customer.
Is there a way of attaching the two reports Mighty.sas7bdat and tumisi.sas7bdat in pdf to their corresponding emails as given in the Customers table?
Name the reports in such a way that the report name is unique by customer and out put. Instead of calling it a generic name custom_report&today.&_pdf_. you can call it custom_report<customername>&today.&_pdf_. or custom_report_<mighty/any uniquename>&today.&_pdf_.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.