Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order

LMSSAS — Tue, 24 May 2022 11:46:39 GMT

hello - I continue to get the following error: ERROR 180-322: Statement is not valid or it is used out of proper order. I have been through this code for a day and can not correct this. The log is below, can someone please take a look and offer any insight. I an sending an email that loops through broker managers name, then their email address and sends that managers report to them. Thank you for your help

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
33         
34         GOPTIONS ACCESSIBLE;
35         %macro hex2(n);
36                                         %local digits n1 n2;
37                                         %let digits = 0123456789ABCDEF;
38                                         %let n1 = %substr(&digits, &n / 16 + 1, 1);
39                                         %let n2 = %substr(&digits, &n - &n / 16 * 16 + 1, 1);
40                                         &n1&n2
41         %mend hex2;
42         %macro RGB(r,g,b);
43                                         %cmpres(CX%hex2(&r)%hex2(&g)%hex2(&b))
44         %mend RGB;
45         
46         proc sql;
47         select distinct brokermanager into: BrokerManager1 separated by " "
48         from brokermanageremail;
49         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

50         
2                                                          The SAS System                                07:30 Tuesday, May 24, 2022

51         %macro reps1;
52         
53         %local i rep ;
54         %do i=1 %to %sysfunc(countw(&BrokerManager1));
55         %let rep = %scan(&BrokerManager1, &i);
56         
57         %put &BrokerManager1.;
58         %put &rep.;
59         
60         proc sql;
61         select distinct Email_Address into :Email_Address trimmed
62         from brokermanageremail
63         where BrokerManager = "&rep1."
64         ;quit;
65         
66         Filename myemail Clear;
67         FILENAME myemail EMAIL
68         sender=("")
69         from=("")
70         /*To = ("&rep_email.")*/
71         CC = ("")
72         bcc =("")
73         Subject = "Reports"
74         attach =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel");
75         content_type="text/html";
76         /*ods msoffice2k file=myemail rs=none style=htmlblue;*/
77         /*options(pagebreak="no");*/
78         data _null_;
79         	file myemail;
80         	put '<body style=font-size:11pt;font-family:Times New Roman>';
81         	put     ' <p>Hello,';
82         	put    '</p> Please see the following attachment for an updated version of the Ready To Sell Broker Manager Report.' ;
83         	put      	'<p> This report is separated into 4 separate tabs.';
84         	put			'<p> 1. Agency Overview: A list of your sub-agencies that are populated with agents. If an agency has no agents,
84       ! that agency will not appear in this Overview.';
85         	put					'<p> a. Agency Name:';
86         	put          		'<p> b. AOR Code: of agency in column A.' ;
87         	put     			'<p> c. Level 3, Level 2 and TOH';
88         	put     					'<p> i. Indicates the level of the agency in relationship to the Top of Hierarchy agency';
89         	put					'<p> d. Agencies will have a line item row for each role in the agency (see column K) and possible a blank for
89       ! 2021';
90         	put					'<p> e. Columns O and P are contract completion and end dates';
91         	put     					'<p> i. Florida does not expire licenses, so more dates are pushed out to year 9999';
92         	put     					'<p> ii. This would be populated with a current date if the agent was terminated by request or due to
92       ! circumstances';
93         	put					'<p> g. L is the column that indicates if the agent has both AHIP and product certification training completed';
94         	put			'<p> 2. AHIP Needed: Agents for whom we have no AHIP transcripts in house, It is the receipt of these transcripts
94       ! that pushes the Product Certification training to the agents AHIP site';
95         	put     	'<p> 3. Ready to Train: Agents for whom we have AHIP on file, but they have not yet completed product
95       ! certification';
96         	put     	'<p> 4. Ready-to-Sell: Agents for whom we have received AHIP transcripts and who have successfully completed
96       ! product certification, These agents are fully certified and Read to Sell';
97         	put		'<p> If you have any questions, please reach out to the Agent Service Center at 800-267-3156, or the Florida Blue
97       ! Broker Agents mailbox at FBMBrokers@bcbsfl.com or your Local Broker Manager, based on the county you live in.';
98                    '<p> If you would like to add anyone to the distribution list for this report, please send an email to the
98       ! Florida Blue Broker Agents mailbox at FBMBrokers@bcbsfl.com and include Agency Name, Agency Code, and Email Address.';
99                    '<p> Thank you for your partnership with Florida Blue Medicare';
100        	
3                                                          The SAS System                                07:30 Tuesday, May 24, 2022

101        
102        
103        run;
104        
105        /*ods msoffice2k close;*/
106        %end;
107        %mend reps1;
108        %reps1;
Key Nguyen Sierra Sweat Theodore
Key
WARNING: Apparent symbolic reference REP1 not resolved.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Fileref MYEMAIL has been deassigned.
NOTE: Line generated by the invoked macro "REPS1".
108         = ("") bcc =("") Subject = "Broker Manager Reports" attach
108      ! =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel"); content_type="text/html";   data
                                                                                                 ____________
                                                                                                 180
108      ! _null_;  file myemail;  put
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> If you would like to add anyone to the distribution list for this report, please send an email to the
                       ___________________________________________________________________________________________________________
                       180
108      !  Agents mailbox at Testemail.com and include Agency Name, Agency Code, and Email Address.';
NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> Thank you for your partnership';        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
      

Key Nguyen Sierra Sweat Theodore
Nguyen
WARNING: Apparent symbolic reference REP1 not resolved.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Fileref MYEMAIL has been deassigned.
NOTE: Line generated by the invoked macro "REPS1".
108         = ("") bcc =("") Subject = "Broker Manager Reports" attach
108      ! =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel"); content_type="text/html";   data
                                                                                                 ____________
4                                                          The SAS System                                07:30 Tuesday, May 24, 2022

                                                                                                 180
108      ! _null_;  file myemail;  put
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> If you would like to add anyone to the distribution list for this report, please send an email to the
                       ___________________________________________________________________________________________________________
                       180
108      !  Agents mailbox at testemail@bcbsfl.com and include Agency Name, Agency Code, and Email Address.';
NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> Thank you for your partnership';        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
      

Key Nguyen Sierra Sweat Theodore
Sierra
WARNING: Apparent symbolic reference REP1 not resolved.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Fileref MYEMAIL has been deassigned.
NOTE: Line generated by the invoked macro "REPS1".
108         = ("") bcc =("") Subject = "Broker Manager Reports" attach
108      ! =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel"); content_type="text/html";   data
                                                                                                 ____________
                                                                                                 180
108      ! _null_;  file myemail;  put
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> If you would like to add anyone to the distribution list for this report, please send an email to the
                       ___________________________________________________________________________________________________________
                       180
108      ! Agents mailbox at Testemal@bcbsfl.com and include Agency Name, Agency Code, and Email Address.';
NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> Thank you for your partnershi';        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
      

5                                                          The SAS System                                07:30 Tuesday, May 24, 2022

Key Nguyen Sierra Sweat Theodore
Sweat
WARNING: Apparent symbolic reference REP1 not resolved.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Fileref MYEMAIL has been deassigned.
NOTE: Line generated by the invoked macro "REPS1".
108         = ("") bcc =("") Subject = "Broker Manager Reports" attach
108      ! =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel"); content_type="text/html";   data
                                                                                                 ____________
                                                                                                 180
108      ! _null_;  file myemail;  put
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> If you would like to add anyone to the distribution list for this report, please send an email to the
                       ___________________________________________________________________________________________________________
                       180
108      ! Agents mailbox at testemail@bcbsfl.com and include Agency Name, Agency Code, and Email Address.';
NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> Thank you for your partnership';        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
      

Key Nguyen Sierra Sweat Theodore
Theodore
WARNING: Apparent symbolic reference REP1 not resolved.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Fileref MYEMAIL has been deassigned.
NOTE: Line generated by the invoked macro "REPS1".
108         = ("") bcc =("") Subject = "Broker Manager Reports" attach
108      ! =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel"); content_type="text/html";   data
                                                                                                 ____________
                                                                                                 180
108      ! _null_;  file myemail;  put
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> If you would like to add anyone to the distribution list for this report, please send an email to the
                       ___________________________________________________________________________________________________________
                       180
6                                                          The SAS System                                07:30 Tuesday, May 24, 2022

108      ! Agents mailbox at Testemail@bcbsfl.com and include Agency Name, Agency Code, and Email Address.';
NOTE: Line generated by the invoked macro "REPS1".
108                    '<p> Thank you for your partnership';        run;
                       _______________________________________________________________
                       180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order

PaigeMiller — Tue, 24 May 2022 11:53:44 GMT

Maybe the problem is this:

73         Subject = "Reports"
74         attach =("/u/&sysuserid./Agency Report_&rep..xlsx" content_type="application/vnd.ms-excel");
75         content_type="text/html";

You have an extra semi-colon.

From now on, when you have errors in a macro, turn on the macro debugging by running this command

options mprint;

and then run the macro again, and show us the LOG. We need to see the ENTIRE log, not selected parts.

Re: Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order

ballardw — Tue, 24 May 2022 14:05:47 GMT

Let's look at some of your code:

    %let rep = %scan(&BrokerManager1, &i);
56         
57         %put &BrokerManager1.;
58         %put &rep.;
59         
60         proc sql;
61         select distinct Email_Address into :Email_Address trimmed
62         from brokermanageremail
63         where BrokerManager = "&rep1."

You define a macro variable REP. You use %put with macro variable REP. You attempt to select values of a variable from the data set with a macro variable REP1 that does not show a definition or value set anywhere.

Suspect you mean to use BrokerManager= "&rep."

topic Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order in SAS Programming

Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order

Re: Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order

Re: Canot solve for SAS Email Error - Statement is not valid or it is used out of proper order