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
... View more
