Hi all, I am receiving a syntax error and I am not sure why. I will post the relevant section of code below & the error I am receiving. I would love to hear your opinions on what you think is going wrong. Thank you in advance.
Code:
proc sql; create table lgd_summ_out as select ................................. from &INFILE. where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE. ( select *, Case When charge_off_bal ne . then "2. Charge off at observation" When exclusion_fraud = 1 then "3. Fraud" When exclusion_stagnant = 1 then "4. Stagnant" When deceased_flag = "Y" then "5. Deceased" else LGD_EXCLUSION_OBS = "Not Excluded" end as LGD_EXCLUSION_OBS from SASPMOHB.UNSEC_IRB_MON_PERF_DER_LN where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE.) group by obs_date; quit;
Error:
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, OR, ORDER, UNION, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
Full error:
1 The SAS System 13:43 Monday, December 5, 2022 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='5. Create LGD Summary Outputs For Report'; 4 %LET _CLIENTPROCESSFLOWNAME='Summary Outputs'; 5 %LET _CLIENTPROJECTPATH='J:\Modelling Team\Risk Models SAS Code\Monitoring and Calibration\Mycroft Unsecured IRB 5 ! Monitoring\Combined_Summary_Output_A010.egp'; 6 %LET _CLIENTPROJECTPATHHOST='WCPFHIDSD641'; 7 %LET _CLIENTPROJECTNAME='Combined_Summary_Output_A010.egp'; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=SVG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 %macro HTML5AccessibleGraphSupported; 15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH; 16 %mend; 17 FILENAME EGHTML TEMP; 18 ODS HTML5(ID=EGHTML) FILE=EGHTML 19 OPTIONS(BITMAP_MODE='INLINE') 20 %HTML5AccessibleGraphSupported MLOGIC(HTML5ACCESSIBLEGRAPHSUPPORTED): Beginning execution. MLOGIC(_SAS_VERCOMP_FV): Beginning execution. MLOGIC(_SAS_VERCOMP_FV): Parameter FMAJOR has value 9 MLOGIC(_SAS_VERCOMP_FV): Parameter FMINOR has value 4 MLOGIC(_SAS_VERCOMP_FV): Parameter FMAINT has value 4 MLOGIC(_SAS_VERCOMP_FV): Parameter VMAJOR has value 0 MLOGIC(_SAS_VERCOMP_FV): Parameter VMINOR has value 0 MLOGIC(_SAS_VERCOMP_FV): Parameter VMAINT has value 0 MLOGIC(_SAS_VERCOMP_FV): %LOCAL MAJOR MLOGIC(_SAS_VERCOMP_FV): %LOCAL MINOR MLOGIC(_SAS_VERCOMP_FV): %LOCAL MAINT MLOGIC(_SAS_VERCOMP_FV): %LOCAL CURMAJ MLOGIC(_SAS_VERCOMP_FV): %LOCAL CURMIN MLOGIC(_SAS_VERCOMP_FV): %LOCAL CURMNT MLOGIC(_SAS_VERCOMP_FV): %LET (variable name is CURMAJ) SYMBOLGEN: Macro variable SYSVLONG resolves to 9.04.01M4P110916 SYMBOLGEN: Macro variable CURMAJ resolves to 9 MLOGIC(_SAS_VERCOMP_FV): %IF condition %eval(&CurMaj EQ V) is FALSE MLOGIC(_SAS_VERCOMP_FV): %LET (variable name is MAJOR) SYMBOLGEN: Macro variable FMAJOR resolves to 9 MLOGIC(_SAS_VERCOMP_FV): %LET (variable name is MINOR) SYMBOLGEN: Macro variable FMINOR resolves to 4 MLOGIC(_SAS_VERCOMP_FV): %LET (variable name is MAINT) SYMBOLGEN: Macro variable FMAINT resolves to 4 MLOGIC(_SAS_VERCOMP_FV): %LET (variable name is CURMIN) SYMBOLGEN: Macro variable SYSVLONG resolves to 9.04.01M4P110916 MLOGIC(_SAS_VERCOMP_FV): %LET (variable name is CURMNT) SYMBOLGEN: Macro variable SYSVLONG resolves to 9.04.01M4P110916 SYMBOLGEN: Macro variable MAJOR resolves to 9 SYMBOLGEN: Macro variable CURMAJ resolves to 9 MLOGIC(_SAS_VERCOMP_FV): %IF condition %eval(&major NE &CurMaj) is FALSE SYMBOLGEN: Macro variable MINOR resolves to 4 SYMBOLGEN: Macro variable CURMIN resolves to 04 MLOGIC(_SAS_VERCOMP_FV): %IF condition %eval(&minor NE &CurMin) is FALSE SYMBOLGEN: Macro variable MAINT resolves to 4 MLOGIC(_SAS_VERCOMP_FV): %IF condition "&maint" = "" is FALSE SYMBOLGEN: Macro variable CURMNT resolves to 4 2 The SAS System 13:43 Monday, December 5, 2022 SYMBOLGEN: Macro variable MAINT resolves to 4 MLOGIC(_SAS_VERCOMP_FV): Ending execution. MLOGIC(HTML5ACCESSIBLEGRAPHSUPPORTED): %IF condition %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 is TRUE NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release. MPRINT(HTML5ACCESSIBLEGRAPHSUPPORTED): ACCESSIBLE_GRAPH MLOGIC(HTML5ACCESSIBLEGRAPHSUPPORTED): Ending execution. 21 ENCODING='utf-8' 22 STYLE=HtmlBlue 23 NOGTITLE 24 NOGFOOTNOTE 25 GPATH=&sasworklocation SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "/sas/saswork2/SAS_work1C6300000372_satcom2p/SAS_work57D100000372_satcom2p/" 26 ; NOTE: Writing HTML5(EGHTML) Body file: EGHTML 27 28 29 30 /* Summary LGD Measures */ 31 proc sql; 32 create table lgd_summ_out as select 33 obs_date, 34 /* Overall LGD - Observation Measures */ 35 sum(case when customer_id ne . then 1 else 0 end) as Total_Base_Volume, 36 sum(case when LGD_EXCLUSION_OBS ne "Not Excluded" then 1 else 0 end) as LGD_Exclusions_Volume_Obs, 37 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" then 1 else 0 end) as LGD_Monitoring_Volume_Obs, 38 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" then ON_BALANCE else 0 end) as Total_Balance_LGD, 39 sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS="Not Excluded" then FLOORED_DOWNTURN_LGD else 0 end) 40 / 41 sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS="Not Excluded" then 1 else 0 end) as Expected_ID_DTLGD_Obs, 42 sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS="Not Excluded" then FLOORED_DOWNTURN_LGD else 0 end) 43 / 44 sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS="Not Excluded" then 1 else 0 end) as Expected_NID_DTLGD_Obs, 45 46 /* STABILITY INDEX */ 47 1.0 as Red_StabInx, 48 0.2 as Amber_StabInx, 49 0.1 as Green_StabInx, 50 51 /* Overall LGD - Performance Measures */ 52 53 /*number of exclusions*/ 54 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF ne "Not Excluded" then 1 else 0 end) as 54 ! LGD_Exclusions_Volume_Perf, 55 56 /*number of non exclusions*/ 57 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then 1 else 0 end) as 57 ! LGD_Monitoring_Volume_Perf, 58 59 /*average LGD=sum PiT LGD/# non-excluded LGD's*/ 60 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then PIT_LGD else 0 end) 61 / 62 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then 1 else 0 end) 63 as Avg_PiT_LGD, 64 65 /*average LGD=sum DT LGD/# non-excluded LGD's*/ 66 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then FLOORED_DOWNTURN_LGD else 0 66 ! end) 67 / 3 The SAS System 13:43 Monday, December 5, 2022 68 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then 1 else 0 end) 69 as Avg_DT_LGD, 70 71 /*average LGD=sum actual LGD/# non-excluded LGD's*/ 72 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then LGD_ACTUAL else 0 end) 73 / 74 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then 1 else 0 end) 75 as Avg_Actual_LGD, 76 77 /*LGD RAG bands based on predictions */ 78 (&Red_Lower_LGD.* calculated Avg_PiT_LGD) as LGD_Lower_Red, SYMBOLGEN: Macro variable RED_LOWER_LGD resolves to 0.9 79 (&Amber_Lower_LGD.* calculated Avg_PiT_LGD)-(&Red_Lower_LGD.* calculated Avg_PiT_LGD) as LGD_Lower_Amber, SYMBOLGEN: Macro variable AMBER_LOWER_LGD resolves to 0.95 SYMBOLGEN: Macro variable RED_LOWER_LGD resolves to 0.9 80 (&Green_LGD.* calculated Avg_PiT_LGD)-(&Amber_Lower_LGD.* calculated Avg_PiT_LGD) as LGD_Green, SYMBOLGEN: Macro variable GREEN_LGD resolves to 1.05 SYMBOLGEN: Macro variable AMBER_LOWER_LGD resolves to 0.95 81 (&Amber_Upper_LGD.* calculated Avg_PiT_LGD)-(&Green_LGD.* calculated Avg_PiT_LGD) as LGD_Upper_Amber, SYMBOLGEN: Macro variable AMBER_UPPER_LGD resolves to 1.1 SYMBOLGEN: Macro variable GREEN_LGD resolves to 1.05 82 &max_axis_LGD. - (&Amber_Upper_LGD.* calculated Avg_PiT_LGD) as LGD_Upper_Red, SYMBOLGEN: Macro variable MAX_AXIS_LGD resolves to 1 SYMBOLGEN: Macro variable AMBER_UPPER_LGD resolves to 1.1 83 84 /*average new realised actual LGD*/ 85 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" and OBS_DATE=DEF_DATE then 85 ! LGD_ACTUAL else 0 end) 86 / 87 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" and OBS_DATE=DEF_DATE then 1 87 ! else 0 end) as Avg_actual_LGD_NewIn, 88 89 /*more actual metrics*/ 90 (sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then 90 ! FLOORED_DOWNTURN_LGD*ON_BALANCE else . end)) as DT_LGD_Act_EAD_Weighted, 91 (sum(case when LGD_EXCLUSION_OBS = "Not Excluded" and LGD_EXCLUSION_PERF = "Not Excluded" then LGD_ACTUAL_VALUE else . 91 ! end)) 92 as Actual_LGD_GBP, 93 (calculated Avg_Actual_LGD - calculated Avg_PiT_LGD) / calculated Avg_Actual_LGD as LGD_Acc_PiT, 94 (calculated Avg_Actual_LGD - calculated Avg_DT_LGD) / calculated Avg_Actual_LGD as LGD_Acc_DT, 95 96 /* Measure required for summary page on obs data. In previous version, we used reg_cap_dtlgd but since PD shows PiT in 96 ! summary, this has been changed to use PIT_LGD. */ 97 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" then PIT_LGD else 0 end) 98 / 99 sum(case when LGD_EXCLUSION_OBS = "Not Excluded" then 1 else 0 end) 100 as Avg_PiT_LGD_OBS, /* EXPECTED_LGD */ 101 102 /*---------------------------------------------------------------------------------------------*/ 103 104 /* NID PCO - Observation Measures */ 105 106 /*---------------------------------------------------------------------------------------------*/ 107 108 /*NID non-excluded observation volume*/ 109 sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" then 1 else 0 end) as 109 ! LGD_NID_Monitoring_Volume_Obs, /* NID_Volume_Obs */ 110 4 The SAS System 13:43 Monday, December 5, 2022 111 /* NID PCO - Performance Measures */ 112 113 /*NID non-excluded performance volume*/ 114 sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 115 and LGD_EXCLUSION_PERF="Not Excluded" 116 then 1 else 0 end) as LGD_NID_Monitoring_Volume_Perf, /* NID_Volume_Perf */ 117 118 /*Average PCO EXECTED (NID non-excluded performance population*/ 119 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 120 and LGD_EXCLUSION_PERF="Not Excluded" 121 then PCO_PCT else 0 end)) 122 / 123 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 124 and LGD_EXCLUSION_PERF="Not Excluded" 125 then 1 else 0 end)) as Avg_PiT_PCO_NID, 126 127 /*Average PCO LATCAL (NID non-excluded performance population*/ 128 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 129 and LGD_EXCLUSION_PERF="Not Excluded" 130 then PCO_LATCAL else 0 end)) 131 / 132 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 133 and LGD_EXCLUSION_PERF="Not Excluded" 134 then 1 else 0 end)) 135 as Avg_Recal_PCO_NID, 136 137 /*Avg_Actual_PCO_NID*/ 138 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 139 and LGD_EXCLUSION_PERF="Not Excluded" 140 and LGD_PCO_PERFORMANCE=1 then 1 else 0 end)) 141 / 142 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 143 and LGD_EXCLUSION_PERF="Not Excluded" 144 then 1 else 0 end)) 145 as Avg_Actual_PCO_NID, 146 147 /*Bads_Actual_PCO_NID*/ 148 (sum(case when L_DEFAULT_FLAG="N" and LGD_EXCLUSION_OBS = "Not Excluded" 149 and LGD_EXCLUSION_PERF="Not Excluded" 150 and LGD_PCO_PERFORMANCE=1 then 1 else 0 end)) 151 as Bads_Actual_PCO_NID, 152 153 /*PCO_Acc_PiT_NID*/ 154 (calculated Avg_Actual_PCO_NID - calculated Avg_PiT_PCO_NID) / calculated Avg_Actual_PCO_NID as PCO_Acc_PiT_NID, 155 156 157 /*--------------------------------------------------------------------------------------------------------*/ 158 159 /* ID PCO - Observation Measures */ 160 161 /*Defaulted, non-excluded, population volume*/ 162 sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" then 1 else 0 end) as 162 ! LGD_ID_Monitoring_Volume_Obs, 163 164 /* ID PCO - Performance Measures */ 165 166 /*non-excluded, defaulted, performance volume*/ 167 sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 5 The SAS System 13:43 Monday, December 5, 2022 168 and LGD_EXCLUSION_PERF="Not Excluded" 169 then 1 else 0 end) as LGD_ID_Monitoring_Volume_Perf, 170 171 /*Avg_PiT_PCO_ID*/ 172 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 173 and LGD_EXCLUSION_PERF="Not Excluded" 174 then PCO_PCT else 0 end)) 175 / 176 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 177 and LGD_EXCLUSION_PERF="Not Excluded" 178 then 1 else 0 end)) as Avg_PiT_PCO_ID, 179 180 /*Avg_Recal_PCO_ID*/ 181 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 182 and LGD_EXCLUSION_PERF="Not Excluded" 183 then PCO_LATCAL else 0 end)) 184 / 185 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 186 and LGD_EXCLUSION_PERF="Not Excluded" 187 then 1 else 0 end)) as Avg_Recal_PCO_ID, 188 189 /*Avg_Actual_PCO_ID*/ 190 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 191 and LGD_EXCLUSION_PERF="Not Excluded" 192 and LGD_PCO_PERFORMANCE=1 then 1 else 0 end)) 193 / 194 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 195 and LGD_EXCLUSION_PERF="Not Excluded" 196 then 1 else 0 end)) 197 as Avg_Actual_PCO_ID, 198 199 /*Bads_Actual_PCO_ID*/ 200 (sum(case when L_DEFAULT_FLAG="Y" and LGD_EXCLUSION_OBS = "Not Excluded" 201 and LGD_EXCLUSION_PERF="Not Excluded" 202 and LGD_PCO_PERFORMANCE=1 then 1 else 0 end)) 203 as Bads_Actual_PCO_ID, 204 205 /*PCO_Acc_PiT_ID*/ 206 (calculated Avg_Actual_PCO_ID - calculated Avg_PiT_PCO_ID) / calculated Avg_Actual_PCO_ID as PCO_Acc_PiT_ID 207 208 from &INFILE. SYMBOLGEN: Macro variable INFILE resolves to UNSEC_IRB_MON_PERF_DER_ln 209 where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE. SYMBOLGEN: Macro variable FIRST_OBS_NUM_DATE resolves to 21427 SYMBOLGEN: Macro variable LAST_OBS_NUM_DATE resolves to 22735 210 ( select *, _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, OR, ORDER, UNION, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored. 211 Case When charge_off_bal ne . then "2. Charge off at observation" 212 When exclusion_fraud = 1 then "3. Fraud" 213 When exclusion_stagnant = 1 then "4. Stagnant" 214 When deceased_flag = "Y" then "5. Deceased" 6 The SAS System 13:43 Monday, December 5, 2022 215 else LGD_EXCLUSION_OBS = "Not Excluded" end as LGD_EXCLUSION_OBS 216 from SASPMOHB.UNSEC_IRB_MON_PERF_DER_LN 217 where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE.) SYMBOLGEN: Macro variable FIRST_OBS_NUM_DATE resolves to 21427 SYMBOLGEN: Macro variable LAST_OBS_NUM_DATE resolves to 22735 218 group by obs_date; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 219 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 220 221 %LET _CLIENTTASKLABEL=; 222 %LET _CLIENTPROCESSFLOWNAME=; 223 %LET _CLIENTPROJECTPATH=; 224 %LET _CLIENTPROJECTPATHHOST=; 225 %LET _CLIENTPROJECTNAME=; 226 %LET _SASPROGRAMFILE=; 227 %LET _SASPROGRAMFILEHOST=; 228 229 ;*';*";*/;quit;run; 230 ODS _ALL_ CLOSE; 231 232 233 QUIT; RUN; 234
This error displays just below the ( select * line.
When you have macro variables, please turn on the macro debugging options by running this one line of code and then run your PROC SQL again.
options symbolgen mprint;
When you get errors in the log, we need to see the ENTIRE log from this PROC SQL (meaning, every single line in the log from PROC SQL, every single character in the log from PROC SQL, nothing omitted). Please copy the log as text and paste it into the window that appears when you click on the </> icon.
Why do you have the WHERE condition twice?
The first one (part of the outer query) is not placed properly. It should be after the FROM clause, like in the inner query.
Also if you are intending to join &INDATA with the result of that subquery you need to specify how to join them.
You could just let SAS do a full "cross join" by inserting a comma between the two.
create table lgd_summ_out as select
.................................
from &INFILE.
, ( select *
, case when charge_off_bal ne . then "2. Charge off at observation"
when exclusion_fraud = 1 then "3. Fraud"
when exclusion_stagnant = 1 then "4. Stagnant"
when deceased_flag = "Y" then "5. Deceased"
else LGD_EXCLUSION_OBS = "Not Excluded"
end as LGD_EXCLUSION_OBS
from SASPMOHB.UNSEC_IRB_MON_PERF_DER_LN
where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE.
)
where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE.
group by obs_date
;
The ELSE clause works like the THEN clause, you just put the result there, not an explicit assignment (like you do in the data step).
elseLGD_EXCLUSION_OBS ="Not Excluded" end as LGD_EXCLUSION_OBS
208 from &INFILE. SYMBOLGEN: Macro variable INFILE resolves to UNSEC_IRB_MON_PERF_DER_ln 209 where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE. SYMBOLGEN: Macro variable FIRST_OBS_NUM_DATE resolves to 21427 SYMBOLGEN: Macro variable LAST_OBS_NUM_DATE resolves to 22735 210 ( select *, _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, OR, ORDER, UNION, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored.
Your code does not generate valid SQL syntax. You get the following partial code generated
where 21427 le obs_date le 22735 (select *,
and this is not valid SQL syntax. That's what the error message is telling you, you can't have a left parenthesis immediately after where 21427 le obs_date le 22735 and so you get an error. The error message even tells you what you can have at this point in the SQL code, things such as AND or OR and so on. So, I leave it up to you to fix, as I don't really know what logic you are trying to implement here.
Also, since your SQL code involves lots of repetitive summing/counting with condition such as case when LGD_EXCLUSION_OBS ne "Not Excluded", you ought to really consider replacing this with PROC SUMMARY or PROC FREQ, which will be much less coding and much easier to debug, and much more readable code. SAS has already done the work of debugging and testing PROC SUMMARY and PROC FREQ so you don't have to.
From the shown code:
where &FIRST_OBS_NUM_DATE. le obs_date le &LAST_OBS_NUM_DATE. ( select *, Case When charge_off_bal ne . then "2. Charge off at observation"
you start a ( after the comparison of "le &last_obs_num_date . Where clause would expect any multiple conditions to have something like an "and" or "or" before the second condition to be syntactically correct to tell the program how to use the remainder of the clause.
Example where the second condition is a variable to be in a list of values
where a le b and c in (1,2,3)
Not saying anything about the approach as shown, just the very specific error.
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.