Re: Why am I getting this syntax error?

mpn · Posted 12-05-2022 08:27 AM

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.

PaigeMiller · Posted 12-05-2022 08:38 AM

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.

--
Paige Miller

Tom · Posted 12-05-2022 11:53 AM

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
;

LinusH · Posted 12-05-2022 09:48 AM

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).

else LGD_EXCLUSION_OBS = "Not Excluded" end as LGD_EXCLUSION_OBS

Data never sleeps

PaigeMiller · Posted 12-05-2022 10:39 AM

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.

--
Paige Miller

ballardw · Posted 12-05-2022 11:26 AM

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.

Why am I getting this syntax error?