BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mmohotsi
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

1 REPLY 1
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 724 views
  • 0 likes
  • 2 in conversation