BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LMSSAS
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

From the SAS documentation

 

CASE <case-operand>

<WHEN when-condition THEN result-expression ...>
END
 
Does that help? Code with ELSE END doesn't seem to be legal SAS syntax
--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

From the SAS documentation

 

CASE <case-operand>

<WHEN when-condition THEN result-expression ...>
END
 
Does that help? Code with ELSE END doesn't seem to be legal SAS syntax
--
Paige Miller
LMSSAS
Quartz | Level 8
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 ,
japelin
Rhodochrosite | Level 12

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.

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 564 views
  • 0 likes
  • 3 in conversation