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;
From the SAS documentation
CASE <case-operand>
<WHEN when-condition THEN result-expression ...><ELSE result-expression>END
From the SAS documentation
CASE <case-operand>
<WHEN when-condition THEN result-expression ...><ELSE result-expression>END
Try changing it like this
when Z.SALS_AGEY_PPTY_TYP_VAL_TX = 'FMOSLVR' THEN 'SILVER'
when Z.SALS_AGEY_PPTY_TYP_VAL_TX = 'FMOGLD' THEN 'GOLD'
when Z.SALS_AGEY_PPTY_TYP_VAL_TX = 'FMOBNZ' THEN 'BRONZE'
when Z.SALS_AGEY_PPTY_TYP_VAL_TX = 'FMOPLT' THEN 'PLATINUM'
else ''
END AS 'AGENCY TIER'n ,
Since there are no quotes, SAS has recognized that FMOSLVR, FMOGLD...PLATINUM
are column names.
Also, after the else, you should specify missing.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.