How to create an error email if report fails?

LMSSAS — Wed, 07 Dec 2022 13:26:36 GMT

Below is the following log with my error:ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &syscc<20 and &y = 0 ERROR: The macro SEND_EMAIL will stop executing. I am creating two proc reports and sending out the tables in an email to my customer. I need to solve for having SAS generate an email if for some reson this report errors out or doen't run, so that I am aware there is an issue with the report. The code I am receiving the error on is lines 167-173. Can some help me understand what this code and error are telling me? I am not expereinced with macros and need additional assistance.

proc format;
38          value paidf .='0' other=[comma10.];
NOTE: Format PAIDF is already on the library WORK.FORMATS.
NOTE: Format PAIDF has been output.
39         run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

40         
41         %macro hex2(n);
42         	%local digits n1 n2;
43         	%let digits = 0123456789ABCDEF;
44         	%let n1 = %substr(&digits, &n / 16 + 1, 1);
45         	%let n2 = %substr(&digits, &n - &n / 16 * 16 + 1, 1);
46         	&n1&n2
47         %mend hex2;
2 The SAS System                                                                                     16:22 Tuesday, December 6, 2022

48         
49         %macro RGB(r,g,b);
50         	%cmpres(CX%hex2(&r)%hex2(&g)%hex2(&b))
51         %mend RGB;
52         
53         /*%let font2 = wh;
54         %let fonttype = Arial;*/
55         
56         /*%let worddt= %sysfunc(today(), worddate20.);*/
57         
58         filename mymail clear;
NOTE: Fileref MYMAIL has been deassigned.
59         title;
60         footnote;
61         filename mymail email
62            Subject = "AEP Sales YoY Comparison"
63            sender=("email.email@bcbsfl.com")
64         /*
64       ! To=("email.email@bcbsfl.com")
65        From = ("email.email@bcbsfl.com")
66            CC = ("email.email@bcbsfl.com")
67      
69             content_type="text/html";
70         
71            ods msoffice2k file=mymail rs=none style=htmlblue options(pagebreak="no");
NOTE: Writing MSOFFICE2K Body file: MYMAIL
72            options nocenter;
73         
74         /*ods excel file="/u/&sysuserid./Daily RTS Summary.xlsx"*/
75         
76         /* options (autofilter="all" sheet_name='Broker Mngr RTS Summary' sheet_interval="none" ) ; */
77         
78         title1 j=l h=2.5 color=black "Hello,";
79             title2 j=l h=2.5 color=black "The below tables include summaries of AEP Sales YoY Comparison YTD for AEP 2023 and AEP
79       !  2022 by date and channel.
80         									Please do not hesitate to reach out if you have any questions.";
81             title3 j=l h=2.5 color=black "Thank you." ;
82         
83         proc report data = OUTPUT2 nowd split='|'
84         style(header) = [font=("Arial",10.0pt) vjust=middle just=center background=%RGB(0,145,204) foreground=whitesmoke
84       ! font_weight=bold]
85         style(column) = [font=("Arial",8.58pt) bordercolor=%RGB(242,242,242) ];
86         column Channel '2022'n '2021'n Difference PERCENT_CHANGE ;
87         
88         define Channel / 'Channel' order=data;
89         define '2022'n / sum "AEP Sales | 2023" style(column)={vjust=c just=c cellwidth = 1in} order=data;
90         define '2021'n / sum "AEP Sales | 2022" style(column)={vjust=c just=c cellwidth = 1in} order=data;
91         define Difference / "YOY | Difference" style(column)={vjust=c just=c cellwidth = 1in} order=data;
92         define PERCENT_CHANGE / '% Change' format=percent8.2;
93         
94         /*rbreak after / summarize style=[fontweight=bold] ;*/
95         
96         compute before _page_/style=[font=("Arial",12.5pt) vjust=middle just=center background=whitesmoke
96       ! foreground=%RGB(0,145,204) font_weight=bold borderbottomcolor=%RGB(242,242,242)];
97         		 line 'AEP Sales YoY Comparison' ; endcomp;
98         
3 The SAS System                                                                                     16:22 Tuesday, December 6, 2022

99         compute Channel;
100        	I + 1;
101        	if mod(i,2) eq 1 then
102        	call define(_row_, "style", "style=[background=%RGB(230,230,230)]");
103        endcomp;
104        
105        /*compute after; Channel = 'Total'; call define(_row_,'style','style={backgroundcolor=lightgray foreground=black
105      ! font_weight=bold}'); endcomp; */
106        
107        compute PERCENT_CHANGE;'% change'n=Difference.sum/'2021'n.sum ; endcomp;
108        
109         run;

NOTE: There were 11 observations read from the data set WORK.OUTPUT2.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.08 seconds
      cpu time            0.02 seconds
      

110        Title;
111        
112        proc report data = OUTPUT3 nowd split='|'
113        style(header) = [font=("Arial",10.0pt) vjust=middle just=center background=%RGB(0,145,204) foreground=whitesmoke
113      ! font_weight=bold]
114        style(column) = [font=("Arial",8.58pt) bordercolor=%RGB(242,242,242) ];
115        column 'Submit Date'n '2022'n '2021'n Difference PERCENT_CHANGE ;
116        
117        define 'Submit Date'n / "Submit Date" order=data;
118        define '2022'n / sum "AEP Sales | 2023" style(column)={vjust=c just=c cellwidth = 1in} order=data;
119        define '2021'n / sum "AEP Sales | 2022" style(column)={vjust=c just=c cellwidth = 1in} order=data;
120        define Difference / "YOY | Difference" style(column)={vjust=c just=c cellwidth = 1in} order=data;
121        define PERCENT_CHANGE / '% Change' format=percent8.2;
122        
123        /*rbreak after / summarize style=[fontweight=bold] ;*/
124        
125        compute before _page_/style=[font=("Arial",12.5pt) vjust=middle just=center background=whitesmoke
125      ! foreground=%RGB(0,145,204) font_weight=bold borderbottomcolor=%RGB(242,242,242)];
126        		 line 'AEP Sales YoY Comparison' ; endcomp;
127        
128        compute 'Submit Date'n;
129        	I + 1;
130        	if mod(i,2) eq 1 then
131        	call define(_row_, "style", "style=[background=%RGB(230,230,230)]");
132        endcomp;
133        
134        /*compute after; 'Submit Date'n = Total; call define(_row_,'style','style={backgroundcolor=lightgray foreground=black
134      ! font_weight=bold}'); endcomp; */
135        
136        compute PERCENT_CHANGE;'% change'n=Difference.sum/'2021'n.sum ; endcomp;
137        
138         run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      9 at 1:27   
NOTE: There were 67 observations read from the data set WORK.OUTPUT3.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
4 The SAS System                                                                                     16:22 Tuesday, December 6, 2022

NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.28 seconds
      cpu time            0.07 seconds
      

139        
140         %macro send_email;
141        %if &syscc<20 and &y = 0 %then %do;
142        
143        title;
144        footnote;
145        filename myemail email
146           Subject = " ERROR: AEP Sales YoY Comparison"
147        	sender=("email.email@bcbsfl.com")
148        	To = ("email.email@bcbsfl.com")
149        	From = ("email.email@bcbsfl.com")
150        	CC = ("email.email@bcbsfl.com")
151            content_type="text/html";
152        
153        data _null_;
154        file myemail;
155        put '<body style=font-size:11pt;font-family:Times New Roman>';
156        put " <p>Hello,";
157        put '</p>';
158        put "<p>There is an error in AEP Sales YoY Comparison.";
159        put '<br>';
160        put '<br>';
161        put "<p>";
162        put '<br>';
163        
164        
165        run;
166        
167        %end;
168        %else %if &syscc<20 and &y> 0 %then %do;
169        
170        %reps2;
171        %end;
172        %mend send_email;
173        %send_email;
WARNING: Apparent symbolic reference Y not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &syscc<20 and &y = 0 
ERROR: The macro SEND_EMAIL will stop executing.
174        
175        
176        GOPTIONS NOACCESSIBLE;
177        %LET _CLIENTTASKLABEL=;
178        %LET _CLIENTPROCESSFLOWNAME=;
179        %LET _CLIENTPROJECTPATH=;
180        %LET _CLIENTPROJECTPATHHOST=;
181        %LET _CLIENTPROJECTNAME=;
182        %LET _SASPROGRAMFILE=;
183        %LET _SASPROGRAMFILEHOST=;
184        
185        ;*';*";*/;quit;run;
186        ODS _ALL_ CLOSE;
Message sent
5 The SAS System                                                                                     16:22 Tuesday, December 6, 2022

      To:          
      Cc:          "email.email@bcbsfl.com"
      Bcc:         
      Subject:     AEP Sales YoY Comparison
      Attachments: 
187        
188        
189        QUIT; RUN;
190

Re: How to create an error email if report fails?

Quentin — Wed, 07 Dec 2022 13:51:00 GMT

This warning (and subsequent errors) are triggered because you have not created a macro variable named Y:

WARNING: Apparent symbolic reference Y not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &syscc<20 and &y = 0 
ERROR: The macro SEND_EMAIL will stop executing.

Your macro send_email references the macro variable Y:

140         %macro send_email;
141        %if &syscc<20 and &y = 0 %then %do;

In order to debug this, you'll need to understand the intent of that %IF statement. What is the purpose of making the email conditional on &y=0 ? What is the meaning of the macro variable Y? Where should the Y macro variable have been created, and why doesn't it exist? You might also consider changing the name of the macro variable Y to something more descriptive.

Re: How to create an error email if report fails?

Kurt_Bremser — Wed, 07 Dec 2022 17:20:09 GMT

No need to check for anything but &SYSCC. As soon as SYSCC is not zero, your code did not run cleanly.

Re: How to create an error email if report fails?

Quentin — Wed, 07 Dec 2022 18:45:55 GMT

But of course if &SYSCC=0 that doesn't mean your results are correct. (couldn't find a maxim for that : )

I'm assuming the check on the missing &Y is a check for some logical error in the data (e.g. an implausible value), which does not result in a syntax error. Of course, if assertions are added to detect such implausible values, they would also result in SYSCC being set.

topic Re: How to create an error email if report fails? in SAS Programming

How to create an error email if report fails?

Re: How to create an error email if report fails?

Re: How to create an error email if report fails?

Re: How to create an error email if report fails?