How to automatically send an email given conditions from other tables

mmohotsi — Tue, 09 Apr 2024 11:43:30 GMT

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

Re: How to automatically send an email given conditions from other tables

yabwon — Tue, 09 Apr 2024 12:00:48 GMT

It looks you are mixing up 4GL and macro language, try this one;

%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;
options emailsys = SMTP;
options emailhost = server_rv.randwater.co.za;
options emailauthprotocol = NONE;

%IF &risk_count > 0 and &Columns > 4  
%then %do;
%do i = 1 %to %sysfunc(countw("&emaillist",','));
filename Outbox email
%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"
;
Data _null_;
file Outbox; 
put 'Good day ';
put ' ';
put 'Kindly find the attached risk report';
put ' ';
put 'Warmest regards';
run;
filename Outbox;
%end;
%end;
 
%else %do;
 
%IF &risk_count = 0 or &Columns <= 4  
%then %do;
filename Outbox email
to = "mmohotsi@randwater.co.za"
from = "mmohotsi@randwater.co.za"
bc = " "
importance = "HIGH"
sensitivity = "CONFIDENTIAL"
subject = "Water Quality Risk report"
;
data _null_;
file Outbox; 
put 'Good day ';
put ' ';
put 'The report was not generated';
put ' ';
put 'Warmest regards';
run;
filename Outbox;

%end; 
%end;
%mend  EmailRisk;
%EmailRisk;

Bart

topic How to automatically send an email given conditions from other tables in SAS Programming

How to automatically send an email given conditions from other tables

Re: How to automatically send an email given conditions from other tables