Case Statement Error

LMSSAS — Tue, 26 Apr 2022 13:10:00 GMT

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;

Re: Case Statement Error

PaigeMiller — Tue, 26 Apr 2022 13:19:16 GMT

From the SAS documentation

CASE <case-operand>

WHEN when-condition THEN result-expression

<WHEN when-condition THEN result-expression ...>

<ELSE result-expression>

END

Does that help? Code with ELSE END doesn't seem to be legal SAS syntax

Re: Case Statement Error

japelin — Tue, 26 Apr 2022 13:27:07 GMT

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.

Re: Case Statement Error

LMSSAS — Tue, 26 Apr 2022 13:33:25 GMT

Yep, thank you for forwarding this. I used that format and it worked. Thank you!!
case Z.SALS_AGEY_PPTY_TYP_VAL_TX
when 'FMOSLVR' THEN 'SILVER'
when 'FMOGLD' THEN 'GOLD'
when 'FMOBNZ' THEN 'BRONZE'
when 'FMOPLT' THEN 'PLATINUM'
END AS 'AGENCY TIER'n ,

Re: Case Statement Error

LMSSAS — Tue, 26 Apr 2022 13:35:12 GMT

Thank you for that information,. I didn't relaize with no quotes SAS reads it as column names.

topic Re: Case Statement Error in SAS Programming

Case Statement Error

Re: Case Statement Error

Re: Case Statement Error

Re: Case Statement Error

Re: Case Statement Error