Good day (Morning)
I am trying to automatically create and send an email to customers based on the following scenario. A table is first created from data set using the following code
proc sql ;
create table Variable_Count as
select libname, memname, nobs as totalrows, nvar as totalcolumns from sashelp.vtable
where libname="WORK" and memname = "RAW_DATA_SET";
quit;
This is done since the variables "totalrows" and "totalclumns" are not constant. Once the table is created, then another table is used which has a non constant number of records.
The following code is then used to try and send an email with a created report:
%macro EmailRisk;
proc sql ;
select count(SAMPLING_POINT)
into: Risk_Count from Tested_Count ;
Proc sql;
select totalcolumns into: Columns from Variable_Count;
quit;
proc sql ;
select distinct REQUESTER_EMAIL into: emaillist separated by ',' from RawData_Frame2;
quit;
Data _null_;
options emailsys = SMTP;
options emailhost = server_rv.randwater.co.za;
options emailauthprotocol = NONE;
filename Outbox email ;
file Outbox
%IF &risk_count > 0 and &Columns > 4
%then %do;
%do i = 1 %to %sysfunc(countw("&emaillist",','));
%end;
%let email=%scan(&emaillist,&i.,%str(,));
to = ("&email.")
from = "mmohotsi@randwater.co.za"
bc = " "
attach = <attachment>
importance = "HIGH"
sensitivity = "CONFIDENTIAL"
subject = "Water Quality Risk report"
%put 'Good day ';
%put ' ';
%put 'Kindly find the attached risk report';
%put ' ';
%put 'Warmest regards';%end;
%else %do;
%IF &risk_count = 0 or &Columns <= 4
%then %do;
to = "mmohotsi@randwater.co.za"
from = "mmohotsi@randwater.co.za"
bc = " "
importance = "HIGH"
sensitivity = "CONFIDENTIAL"
subject = "Water Quality Risk report"
%put 'Good day ';
%put ' ';
%put 'The report was not generated';
%put ' ';
%put 'Warmest regards';%end;
%end;
%mend EmailRisk;
%EmailRisk;
The following is the log window from the submitted code:
The SAS System
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program 5';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='C:\Users\mmohotsi\OneDrive - Rand Water\Documents\WQS Section\P O
5 ! C\ZB_POC_ENHANCMENTS\ZB_POC_ENHANCEMENTS.egp';
6 %LET _CLIENTPROJECTPATHHOST='MMOHOTSI-X7997';
7 %LET _CLIENTPROJECTNAME='ZB_POC_ENHANCEMENTS.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 ODS LISTING GPATH=&sasworklocation;
18 FILENAME EGHTML TEMP;
19 ODS HTML5(ID=EGHTML) FILE=EGHTML
20 OPTIONS(BITMAP_MODE='INLINE')
21 %HTML5AccessibleGraphSupported
22 ENCODING='utf-8'
23 STYLE=HTMLBlue
24 NOGTITLE
25 NOGFOOTNOTE
26 GPATH=&sasworklocation
27 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28 FILENAME EGPDF TEMP;
29 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=Pearl;
NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF".
30 FILENAME EGSR TEMP;
31 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
32 STYLE=HTMLBlue
33 NOGTITLE
34 NOGFOOTNOTE
35 GPATH=&sasworklocation
36 ENCODING=UTF8
37 options(rolap="on")
38 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
39
40
41 %macro EmailRisk;
42 proc sql ;
43 select count(SAMPLING_POINT)
44 into: Risk_Count from Tested_Count ;
45 Proc sql;
46 select totalcolumns into: Columns from Variable_Count;
47 quit;
48
49 proc sql ;
50 select distinct REQUESTER_EMAIL into: emaillist separated by ',' from RawData_Frame2;
51 quit;
52 Data _null_;
53 options emailsys = SMTP;
54 options emailhost = rvexchsrv02.randwater.co.za;
The SAS System
55 options emailauthprotocol = NONE;
56 filename Outbox email ;
57 file Outbox
58 %IF &risk_count > 0 and &Columns > 4
59 %then %do;
60 %do i = 1 %to %sysfunc(countw("&emaillist",','));
61 %end;
62 %let email=%scan(&emaillist,&i.,%str(,));
63 to = ("&email.")
64 from = "mmohotsi@randwater.co.za"
65 bc = " "
66 attach = <attachment>
67 importance = "HIGH"
68 sensitivity = "CONFIDENTIAL"
69 subject = "Water Quality Risk report"
70
71 %put 'Good day ';
72 %put ' ';
73 %put 'Kindly find the attached risk report';
74 %put ' ';
75 %put 'Warmest regards';%end;
76
77 %else %do;
78
79 %IF &risk_count = 0 or &Columns <= 4
80 %then %do;
81 to = "mmohotsi@randwater.co.za"
82 from = "mmohotsi@randwater.co.za"
83 bc = " "
84 importance = "HIGH"
85 sensitivity = "CONFIDENTIAL"
86 subject = "Water Quality Risk report"
87
88 %put 'Good day ';
89 %put ' ';
90 %put 'The report was not generated';
91 %put ' ';
92 %put 'Warmest regards';%end;
93 %end;
94 %mend EmailRisk;
95 %EmailRisk;
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.33 seconds
cpu time 0.00 seconds
NOTE: The PROCEDURE SQL printed page 2.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.00 seconds
NOTE: The PROCEDURE SQL printed page 3.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.00 seconds
The SAS System
'Good day '
' '
'The report was not generated'
' '
'Warmest regards'
96
97
98 %LET _CLIENTTASKLABEL=;
99 %LET _CLIENTPROCESSFLOWNAME=;
100 %LET _CLIENTPROJECTPATH=;
101 %LET _CLIENTPROJECTPATHHOST=;
102 %LET _CLIENTPROJECTNAME=;
103 %LET _SASPROGRAMFILE=;
104 %LET _SASPROGRAMFILEHOST=;
105
106 ;*';*";*/;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.00 seconds
cpu time 0.00 seconds
107 ODS _ALL_ CLOSE;
NOTE: ODS PDF(EGPDF) printed 3 pages to
C:\Users\mmohotsi\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-21416-0cad17d0\contents\SAS Temporary
Files\_TD18000_MMOHOTSI-X7997_\#LN00777.
108
109
110 QUIT; RUN;
111
Click image to register for webinar
