Hello - below is my code from the log with the error. See lines 68-74. I cannot figure out why I am getting the ERROR 73-322: Expecting an END.
Can someone take a look at my Case Statement and tell me what I am doing wrong? I only want the variables listed is the case statement to change. If the variable falls outside of the four listed then I don't want anything to change, which is why i didn't list an alternative for "else".
FYI - I tried running the code without the else and got a different error (ERROR: The following columns were not found in the contributing tables: BRONZE, FMOBNZ, FMOGLD, FMOPLT, FMOSLVR, GOLD, PLATINUM,
SILVER.).
proc sql;
62 create table TEST as
63 select distinct
64 upcase(S.SALS_AGEY_NM) AS AGENCY_NAME,
65 H.AGEY_OF_REC_CD AS AOR_CODE,
66 SA.SALS_AGEY_TYP_CD AS AGENCY_TYPE,
67 Z.SALS_AGEY_PPTY_TYP_VAL_TX AS AGENCY_TIER,
68 case
69 when Z.SALS_AGEY_PPTY_TYP_VAL_TX = FMOSLVR THEN SILVER
70 when Z.SALS_AGEY_PPTY_TYP_VAL_TX = FMOGLD THEN GOLD
71 when Z.SALS_AGEY_PPTY_TYP_VAL_TX = FMOBNZ THEN BRONZE
72 when Z.SALS_AGEY_PPTY_TYP_VAL_TX = FMOPLT THEN PLATINUM
73 else
74 END AS 'AGENCY TIER'n ,
__
73
ERROR 73-322: Expecting an END.
75 /* Z.SALS_AGEY_PPTY_TYP_CD AS TIER_CODE,*/
76 /* Z.BUS_ETY_TYP_CD AS BUSINESS_ENTITY,*/
77 /* Z.SALS_AGEY_PPTY_SAR_DT AS LEVEL_START_DATE,*/
78 /* Z.SALS_AGEY_PPTY_END_DT AS LEVEL_END_DATE,*/
79 E.GIVN_NM AS AGENTS_NAME,
80 E.SRNM_NM AS AGENTS_LAST_NAME,
81 compbl(strip(cat(propcase(E.GIVN_NM)," ",propcase(E.SRNM_NM)))) as Agent_Name,
82 B.SALS_AGET_CD AS AGENT_INDIVIDUAL_WRITING_NUMBER,
83 L.SALS_AGET_LIC_ID AS LICENSE,
84 /* EMIL.EMIL_AD_TX AS AGENT_EMAIL_ADDRESS,*/
85 /* PHONE.FULL_TLP_NB_TX AS AGENT_WORK_PHONE_NUMBER,*/
86 C.AGEY_INDV_CHNL_PTNR_ROLE_CD AS ROLE,
3 The SAS System 08:52 Tuesday, April 26, 2022
87 c.sals_agey_pstn_role_sar_dt AS AGENT_START_DATE format=mmddyy10.,
88 c.sals_agey_pstn_role_end_dt AS AGENT_END_DATE format=mmddyy10.,
89 E.NATL_PDCR_NB AS AGENT_NPN,
90 /* A.CRDL_STAT_CD AS CMS_PASS_FAIL,*/
91 /* A.SALS_AGET_CRDL_EFCV_DT AS CMS_CERT_EFF_DATE,*/
92 /* A.SALS_AGET_CRDL_EPRN_DT AS CMS_CERT_TERM_DATE,*/
93 /* upcase(A.CSE_TTL_NM) AS COURSE_TITLE,*/
94 /* PTAL.AD_LN_1_TX AS AGENT_ADDRESS,*/
95 /* PTAL.AD_LN_2_TX AS AGENT_ADDRESS_LINE_2,*/
96 PTAL.CTY_NM,
97 PTAL.USPS_STT_CD,
98 SUBSTR(PTAL.PTAL_CD,1,5) AS ZIP_CODE,
99 x.county_code AS County_CD,
100 PROPCASE(X.COUNTY) AS COUNTY,
101 /* "USA" AS COUNTRY_NAME,*/
102 /* PTAL.LAT_NB,*/
103 /* PTAL.LONG_NB,*/
104 case
105 when a.cse_ttl_nm in ('2022 AEP Medicare Certification') THEN 'READY TO SELL 2022'
106 when a.cse_ttl_nm in ('2023 AEP Medicare Certification') THEN 'READY TO SELL 2023'
107 else "" end AS 'CERTIFICATION_STATUS'n,
108 case
109 WHEN PROPCASE(X.COUNTY) IN
109 ! ('Alachua','Baker','Bay','Bradford','Clay','Dixie','Franklin','Gadsden','Gilchrist','Glades'
109 ! ,'Highlands','Jefferson','Lafayette','Monroe' ,'Okaloosa' ,'Putnam' ,'Santa Rosa','Suwannee','Taylor','Union' ,'Wakulla'
109 ! ,'Walton' ,'Washington') THEN '20'
110 WHEN PROPCASE(X.COUNTY) = 'Brevard' THEN '50'
111 WHEN PROPCASE(X.COUNTY) IN ( 'Calhoun' ,'Charlotte' ,'Citrus', 'Columbia' ,'DeSoto', 'Escambia', 'Flagler' ,'Gulf',
111 ! 'Hamilton' , 'Hardee' ,'Hendry' ,'Hernando' ,'Holmes' ,'Indian River', 'Jackson' ,'Lee' ,'Leon' ,'Levy', 'Liberty',
111 ! 'Madison', 'Manatee' ,'Marion','Martin','Nassau', 'Okeechobee','Osceola', 'Pasco', 'Pinellas' ,'Polk', 'St. Johns', 'St.
111 ! Lucie', 'Sarasota' ,'Seminole', 'Sumter', 'Volusia') THEN ' 58'
112 WHEN PROPCASE(X.COUNTY) = 'Collier ' THEN '75'
113 WHEN PROPCASE(X.COUNTY) = 'Palm Beach' THEN '100'
114 WHEN PROPCASE(X.COUNTY) = 'Lake ' THEN '136'
115 WHEN PROPCASE(X.COUNTY) IN ('Duval', 'Hillsborough', 'Orange') THEN '200'
116 WHEN PROPCASE(X.COUNTY) = 'Broward ' THEN '250'
117 WHEN PROPCASE(X.COUNTY) = 'Miami-Dade ' THEN '300'
118 else "" end AS RTS_GOALS_PER_COUNTY,
119 case
120 WHEN PROPCASE(X.COUNTY) IN ('Collier', 'Miami-Dade', 'Monroe') THEN 'Miami-Dade/ Collier'
121 WHEN PROPCASE(X.COUNTY) IN ('Broward','Indian River', 'Martin', 'Okeechobee', 'Palm Beach', 'St.Lucie','St. Lucie')
121 ! THEN 'South Region'
122 WHEN PROPCASE(X.COUNTY) IN ('Charlotte', 'DeSoto', 'Glades', 'Hardee',
122 ! 'Hendry','Highlands','Lee','Manatee','Sarasota') THEN 'South-West Region'
123 WHEN PROPCASE(X.COUNTY) IN ('Citrus', 'Hernando', 'Hillsborough','Pasco','Pinellas','Polk') THEN 'West Region'
124 WHEN PROPCASE(X.COUNTY) IN ('Brevard', 'Flagler', 'Lake','Marion','Orange','Osceola','Seminole','Sumter','Volusia')
124 ! THEN 'Central Region'
125 else 'North Region' end as BROKER_MANAGERS,
126 /* Region '1' = Miami-Dade/ Collier
127 Region '2' = South Region
128 Region '3' = South-West Region
129 Region '4' = West Region
130 Region '5' = Central Region
131 Region '6' = North Region */
132 case
133 when calculated broker_managers in ('Miami-Dade/ Collier') then "Alexandra Nguyen"
134 when calculated broker_managers in ('South Region') then "Fadner Theodore"
135 when calculated broker_managers in ('South-West Region') then "Don Kruthers"
4 The SAS System 08:52 Tuesday, April 26, 2022
136 when calculated broker_managers in ('West Region') then "Lance Sweat"
137 when calculated broker_managers in ('Central Region') then "Heidi Sierra"
138 when calculated broker_managers in ('North Region') then "Sale Key"
139 end as ASSIGNED_BROKER_MANAGER
140
141 FROM (((((((((CPM1P1.indv_chnl_ptnr E
142 LEFT JOIN CPM1P1.sals_aget_crdl A ON (E.indv_chnl_ptnr_id = A.indv_chnl_ptnr_id))
143 LEFT JOIN CPM1P1.sals_agey_pstn_role C ON (E.indv_chnl_ptnr_id = C.indv_chnl_ptnr_id))
144 LEFT JOIN CPM1P1.sals_agey_pstn B ON (b.indv_chnl_ptnr_id = E.indv_chnl_ptnr_id
145 and c.sals_agey_id = b.sals_agey_id))) /*needs a 2 part join on sals agey id*/
146 LEFT JOIN CPM1P1.indv_chnl_ptnr_emil_ad EMIL ON (E.indv_chnl_ptnr_id = emil.indv_chnl_ptnr_id))
147 LEFT JOIN CPM1P1.indv_chnl_ptnr_tlp_nb PHONE ON (E.indv_chnl_ptnr_id = phone.indv_chnl_ptnr_id) and (tlp_nb_typ_cd =
147 ! 'WORK_PHONE')
148 RIGHT JOIN cpm1p1.sals_agey H ON (H.sals_agey_id = B.sals_agey_id))
149 RIGHT JOIN (select * from CPM1P1.sals_agey_nm
150 where SALS_AGEY_NM_TYP_CD = "ENTITY") S ON (H.sals_agey_id = S.sals_agey_id) /*need to filter only to
150 ! entity */
151 LEFT JOIN CPM1P1.sals_aget_lic L ON (E.indv_chnl_ptnr_id = L.indv_chnl_ptnr_id))
152 LEFT JOIN CPM1P1.sals_agey SA ON (s.sals_agey_id = SA.sals_agey_id))
153 LEFT JOIN CPM1P1.sals_agey_ppty Z ON (Z.sals_agey_id = H.sals_agey_id)
154 LEFT JOIN CPM1P1.indv_chnl_ptnr_ptal_ad PTAL ON (E.INDV_CHNL_PTNR_ID = PTAL.INDV_CHNL_PTNR_ID)
155 and (E.TENANT_ID = PTAL.TENANT_ID) and (AD_TYP_CD = 'HOME_ADDR_CNTC')
156 LEFT JOIN locationXref X ON (SUBSTR(PTAL.PTAL_CD,1,5) = PUT(X.ZIP_CODE,5.)))
157 where
158 (h.agey_of_rec_cd in
158 ! ('S001','S006','S009','S010','S011','S012','S013','S014','S015','S016','S017','S018','S019','S020','S021','S022','S023','
158 ! S024','S025','S026')
159 or h.agey_of_rec_cd like ('K%')
160 or h.agey_of_rec_cd like ('k%'))
161 /*AND S.SALS_AGEY_NM NE ('EXTEND INSURANCE SERVICES, LLC', 'TZ INSURANCE SOLUTIONS, LLC' , 'TELETECH', 'BROADPATH,
161 ! INC')*/
162 /*AND S.SALS_AGEY_NM ^= ('EXTEND INSURANCE SERVICES, LLC', 'TZ INSURANCE SOLUTIONS, LLC' , 'TELETECH', 'BROADPATH,
162 ! INC')*/
163 /*and h.agey_of_rec_cd not in ('K001','K003')*/
164 /* AND PHONE.TLP_NB_TYP_CD = 'HOME_PHONE'*/
165 /* AND PTAL.AD_TYP_CD = 'HOME_ADDR_CNTC'*/
166 and C.AGEY_INDV_CHNL_PTNR_ROLE_CD in ('Agent')
167 and (c.sals_agey_pstn_role_end_dt ge Today() /*needs parenthesis in where to group and/ors*/
168 or c.sals_agey_pstn_role_end_dt is null)
169 and (z.SALS_AGEY_PPTY_END_DT ge Today() or z.SALS_AGEY_PPTY_END_DT is Null);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
170 quit;
