topic Re: Fetching an email address from an external table in SAS Programming

Fetching an email address from an external table

mmohotsi — Mon, 22 Jun 2020 12:55:01 GMT

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

Re: Fetching an email address from an external table

Reeza — Mon, 22 Jun 2020 15:50:25 GMT

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

Re: Fetching an email address from an external table

smantha — Mon, 22 Jun 2020 21:26:21 GMT

%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;

Re: Fetching an email address from an external table

mmohotsi — Mon, 29 Jun 2020 17:38:18 GMT

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?

Re: Fetching an email address from an external table

smantha — Mon, 29 Jun 2020 20:44:58 GMT

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_.

Re: Fetching an email address from an external table

mmohotsi — Tue, 30 Jun 2020 10:51:11 GMT

thanks for the response
When I created a data set as:
ods pdf file="&_POC_\Customer_Report&Today&_pdf_";

I had challenges creating a unique data for each report for each customer using the customer name as the name of the report

Can SAS generate a unique name for me that corresponds to the Data set for Mighty or Tumisi as you indicated that will be send to the corresponding emails?