BookmarkSubscribeRSS Feed
F23D
Calcite | Level 5

My apologies in advance for not having the right technical verbiage!

 

I am trying to convert a SELECT statement from Netezza to a PROC SQL statement. I am receiving the ERROR 22-322 with the code below:

 

extract(epoch from (CASE WHEN {d '2017-11-27'}='2000-01-01' THEN current_date-1

ELSE {d '2017-11-27'} END)- (AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT)),

 

What am I missing?

5 REPLIES 5
ballardw
Super User

When asking questions about an error it is best to copy the log with the entire procedure/ data step code and error message and paste them into a code box opened with the forum {I} menu icon.

The error diagnostics often show a specific location in the code that created the error message and the position is important. However this forum will reformat the text and the underscore character will appear in the wrong place if not pasted into a code box.

The entire proc or data step is important because the actual cause could be incorrect syntax such as missing ; , unclosed () or ' ' or " " pairs or something else.

 

Likely issues Extract; use of { and } (most SAS expressions only allow () ) ; I have no idea what d '2017-11-27' should do so I suspect that will be an issue

F23D
Calcite | Level 5

Thank you, ballard2! My apologies again...this is the first I have utilized SAS Community and really appreciate your feedback.  I will post the log with the entire procedure.

26         /*SASSQL*/


27         PROC SQL;
28         CREATE TABLE WORK.TEST AS
29         
30         SELECT
31           AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_ID,
32           DT_Last_Occurence.USAA_VENDOR_FILE_CREATION_DT,
33           AT_MDS_FC_CLHE_BKRPY.AUTO_DR_IND,
34           AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT,
35           AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LEGAL_STATUS_CD,
36           AT_MDS_FC_CLHE_BKRPY.ACCOUNT_PROCESSING_STATUS_CD,
37         
38           AT_BK_DM_DATE.CALENDAR_DT,
39           AT_BK_DM_DATE.CALENDAR_MONTH_CD,
40           AT_BK_DM_DATE.CALENDAR_MONTH_DC,
41           AT_BK_DM_DATE.CALENDAR_YEAR_NR,
42         
43           AT_MDS_FC_CLHE_BKRPY.CF_CR_SCORE,
44           AT_MDS_FC_CLHE_BKRPY.CACS_ACCT_CHARGEOFF_IND,
45           AT_MDS_FC_CLHE_BKRPY.CACS_BANKRPTCY_IND,
46           AT_MDS_FC_CLHE_BKRPY.FIRST_ALS_BKPY_SEEN_DT,
47           AT_MDS_FC_CLHE_BKRPY.CACS_BOC_COLLECTION_STATUS_CD,
48           AT_MDS_FC_CLHE_BKRPY.CACS_LAST_ACTIVITY_DT,
49           AT_MDS_FC_CLHE_BKRPY.CACS_COLLECTION_STATUS_CD,
50           AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD1,
51           AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD3,
52           AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD4,
53           AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD2,
54           AT_MDS_FC_CLHE_BKRPY.CDS_BK_CONVERSION_DT,
2                                                          The SAS System                         10:10 Wednesday, November 29, 2017

55           AT_MDS_FC_CLHE_BKRPY.CDS_DISMISSAL_DT,
56           AT_MDS_FC_CLHE_BKRPY.CDS_DOCKET_DATE,
57           AT_MDS_FC_CLHE_BKRPY.CDS_DISCHARGE_DT,
58           AT_MDS_FC_CLHE_BKRPY.CDS_CHAPTER,
59           AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE,
60           AT_MDS_FC_CLHE_BKRPY.CDS_HEARING_DATE,
61           AT_MDS_FC_CLHE_BKRPY.CDS_REAFFIRMATION_DT,
62           AT_MDS_FC_CLHE_BKRPY.CDS_WITHDRAWAL_DT,
63           AT_MDS_FC_CLHE_BKRPY.CDS_SECONDARY_MEMBER_NUM,
64           AT_MDS_FC_CLHE_BKRPY.COLLATERAL_YEAR_NR1,
65           AT_MDS_FC_CLHE_BKRPY.COLLATERAL_DESCRIPTION_TXT1,
66           AT_MDS_FC_CLHE_BKRPY.COLLATERAL_MAKE_TXT1,
67           AT_MDS_FC_CLHE_BKRPY.COLLATERAL_MODEL_TXT1,
68           AT_MDS_FC_CLHE_BKRPY.COLLATERAL_SHORT_DESC_TXT1,
69           AT_MDS_FC_CLHE_BKRPY.COLLATERAL_REFERENCE_ID1,
70           AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND,
71           AT_MDS_FC_CLHE_BKRPY.CUSTOMER_LOAN_TYPE_CD,
72           AT_MDS_FC_CLHE_BKRPY.CHECKSUM_NR,
73           extract(epoch from (CASE WHEN {d '2017-11-27'}='2000-01-01' THEN  current_date-1
                           ____            _
                           22              200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, 
              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 200-322: The symbol is not recognized and will be ignored.

73       !   extract(epoch from (CASE WHEN {d '2017-11-27'}='2000-01-01' THEN  current_date-1
                                           _
                                           22
74          ELSE  {d '2017-11-27'}  END)- (AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT)),
                  _
                  22
                  200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^, 
              ~.  

ERROR 200-322: The symbol is not recognized and will be ignored.

75         
76           extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01' THEN  current_date-1
                             ____             _
                             22               200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, 
              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 200-322: The symbol is not recognized and will be ignored.

76       !   extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01' THEN  current_date-1
                                              _
                                              22
77          ELSE  {d '2017-11-27'}  END)- ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE)),
                  _
                  22
                  200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^, 
              ~.  
3                                                          The SAS System                         10:10 Wednesday, November 29, 2017


ERROR 200-322: The symbol is not recognized and will be ignored.

78         
79           AT_MDS_FC_CLHE_BKRPY.DERIVED_EXCEPTION_IND,
80           AT_MDS_FC_CLHE_BKRPY.FIRST_LOAN_PAYMENT_DUE_DT,
81           AT_MDS_FC_CLHE_BKRPY.HOME_EQUITY_FILE_LOCATION_TXT,
82           AT_MDS_FC_CLHE_BKRPY.LOAN_OLDEST_PAYMENT_DUE_DT,
83           AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD,
84           AT_MDS_FC_CLHE_BKRPY.LOAN_INTEREST_RATE_PCT,
85           AT_MDS_FC_CLHE_BKRPY.LIFE_CYCLE_STATUS_CD,
86           AT_MDS_FC_CLHE_BKRPY.ORIGINAL_PLANNED_END_DT,
87           AT_MDS_FC_CLHE_BKRPY.ORIGINAL_EFFECTIVE_DT,
88           AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY,
89           AT_MDS_FC_CLHE_BKRPY.PROCESSING_STATUS_SET_DT,
90           AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_SK,
91           AT_MDS_FC_CLHE_BKRPY.STOP_ADVANCE_EXIST_IND,
92           AT_MDS_FC_CLHE_BKRPY.SECOND_LEV_LOAN_HIR_BRANCH_CD,
93           AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_ID_TYPE_CD,
94           AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_PD_ID,
95           AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_PD_ID_SEC,
96           AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_PD_TYPE_CD_SEC,
97           AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT,
98           AT_MDS_FC_CLHE_BKRPY.WORKOUT_CODE_3_TXT,
99         
100          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_TYPE_DC,
101          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_TYPE_CD,
102          AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_CHAPTER_CD,
103          AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_CHAPTER_DC,
104          AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND,
105        
106          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_STATUS_CD,
107          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_STATUS_DESC_TXT,
108          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_AREA_CD,
109          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_AREA_DESC_TXT,
110          AT_BK__BOC_COLLECTION_STATUS.MAR_ELIGIBLE_IND,
111          AT_BK__BOC_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_CD,
112          AT_BK__BOC_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_GROUP_CD,
113        
114          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_STATUS_CD,
115          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_STATUS_DESC_TXT,
116          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_AREA_CD,
117          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_AREA_DESC_TXT,
118          AT_BK_CAS_COLLECTION_STATUS.MAR_ELIGIBLE_IND,
119          AT_BK_CAS_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_CD,
120          AT_BK_CAS_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_GROUP_CD,
121        
122          last_day(( AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )),
123          AT_BK_CACS_CUSTOMER.CUST_FIRST_NM,
124          AT_BK_CACS_CUSTOMER.CUST_LAST_NM,
125          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_STREET_1_TXT,
126          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_CITY_NM,
127          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_STATE_CD,
128          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_ZIP_CD,
129          AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD,
130          AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_DC,
131          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_ROLLUP_CD,
132          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_ROLLUP_DC,
4                                                          The SAS System                         10:10 Wednesday, November 29, 2017

133          ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
                                                                               ____
                                                                               22
133      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

134        
135        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ),
136          AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND,
137          extract ( epoch from ( last_day(( AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
                             ____
                             22
137      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ),
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, 
              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

138          case when  ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY ) >60 then 1 else 0 end,
139          AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_PRI_CD,
140          case when ( AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then 1 else 0 end,
141          AT_MDS_FC_CLHE_BKRPY.ACCOUNT_AUTO_DRAFT_DT,
142          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when ( AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then
142      !  1 else 0 end )=1 then 1 when  (
143        
144        AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102  and ( AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND )=1 then 1 else 0 end,
145          case when ( extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01'
                                         ____             _
                                         22               200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 200-322: The symbol is not recognized and will be ignored.

145      !   case when ( extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01'
                                                          _
                                                          22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^, 
              ~.  

146        THEN  current_date-1
147         ELSE  {d '2017-11-27'}  END )- ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) ) )>60 then 1 else 0 end,
                  _
                  22
                  200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^, 
              ~.  

ERROR 200-322: The symbol is not recognized and will be ignored.

148          case when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END,
149          case when  ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY ) >0 then 0 else 1 end,
150          AT_BANKRUPTCY_LEGAL_STATUS.LEGAL_STATUS_DC,
151          case when( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_PROCESSING_STATUS_CD ) IN ('CF','PC')   then (
151      ! AT_MDS_FC_CLHE_BKRPY.PROCESSING_STATUS_SET_DT ) end,
152          case when ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) is null and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=1 then 1
153        when ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE )>(CASE WHEN {d '2017-11-27'}='2000-01-01'
5                                                          The SAS System                         10:10 Wednesday, November 29, 2017

                                                                    _
                                                                    22
                                                                    200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^, 
              ~.  

ERROR 200-322: The symbol is not recognized and will be ignored.

154        THEN  current_date-1
155         ELSE  {d '2017-11-27'}  END) then 1 else 0 end,
                  _
                  22
                  200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^, 
              ~.  

ERROR 200-322: The symbol is not recognized and will be ignored.

156          AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND,
157          case when ( AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=1 and ( case
157      ! when
158        
159        AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END ) =1 and ( case when (
159      ! AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when (
160        
161        AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then 1 else 0 end )=1 then 1 when  ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102
161      !   and ( AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND )=1 then
162        
163        1 else 0 end )=0 and ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) is not null then 1 else 0 end,
164           case when ( AT_BK_CAS_COLLECTION_STATUS.COLLECTION_AREA_CD )='Z' and ((
164      ! substr(AT_MDS_FC_CLHE_BKRPY.CACS_COLLECTION_STATUS_CD,2,2)
165         ) between '10 ' and '99') and ( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_PROCESSING_STATUS_CD ) NOT IN (' CF','PC') and ( extract (
165      ! epoch from last_day(( CASE WHEN {d '2017-11-27'}
                 ____
                 22
                 76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, 
              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, 
              ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

166        
167        ='2000-01-01'
168        THEN  current_date-1
169         ELSE  {d '2017-11-27'}  END
170         ))- ( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT ) ) )>60 and ( extract ( epoch from last_day(( CASE WHEN {d
170      ! '2017-11-27'}='2000-01-01'
171        THEN  current_date-1
172         ELSE  {d '2017-11-27'}  END
173         ))- ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) ) ) >60  and AT_MDS_FC_CLHE_BKRPY.PCL_CUR_CHRG_OFF_BALANCE_AMT=0 and
173      ! AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 then
174        
175        1 else 0 end,
176          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10103 and ( AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( case
176      ! when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT
6                                                          The SAS System                         10:10 Wednesday, November 29, 2017

177        
178        >0 THEN 1 ELSE 0 END )=1 and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=0 and ( (
178      ! AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
179        
180        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- ( AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ) )>=120
180      ! then 1 else 0 end,
181          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( case
181      ! when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT
182        
183        >0 THEN 1 ELSE 0 END )=1 and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=0 and ( case when (
183      ! AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when (
184        
185        AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then 1 else 0 end )=1 then 1 when  ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102
185      !   and ( AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND )=1 then
186        
187        1 else 0 end )=0 and ( ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
187      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
188        
189        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ) )>=1280 then 1 else 0 end,
190          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when
190      ! AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END )=1  and (
191        
192        AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )>=60 and ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY ) <=120 and ( case
192      ! when
193        
194        AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END )>=2500 then 1 else 0 end,
195          AT_BANKTUPTCY_PRODUCT_TYPE.RT_ACCOUNT_TYPE_DC,
196          AT_BANKTUPTCY_PRODUCT_TYPE.RT_ACCOUNT_TYPE_CD,
197          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102  and ( case when ( AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S'
197      ! then 1 else 0 end )=0 and (
198        
199        AT_BANKRUPTCY_LEGAL_STATUS.LEGAL_STATUS_CD ) in ('11', '13', '1A', '1D', '1P', '1S', '2A', '2D', '2P', '2S', '3A', '3D',
199      ! '3P', '3S', '7A', '7D', '7P', '7S','3M','3U','3V',
200        
201        '7L','7V','CN','EL') and ( case when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END )=1 and (
201      ! AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( (
202        
203        AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
203      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
204        
205        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ) )>=180 then 1 else 0 end,
206          AT_BANKRUPCY_CLAIM_TYPE.USER_INPUT_UG_CLAIM_TYPE_DC,
207          AT_BK_DM_DELINQUENCY_BUCKET.BUCKET_DESCRIPTION_TXT,
208          AT_BK_DM_DELINQUENCY_BUCKET.DELINQUENCY_BUCKET_NR,
209          SUM(AT_MDS_FC_CLHE_BKRPY.CACS_CTD_DELINQUENT_AMT),
210          SUM(AT_MDS_FC_CLHE_BKRPY.LOAN_PAYOFF_AMT),
211          SUM(AT_MDS_FC_CLHE_BKRPY.ORIGINAL_ACCOUNT_BALANCE_AMT),
212          SUM(AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT),
213          count(AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_ID),
214          SUM(AT_MDS_FC_CLHE_BKRPY.PCL_CUR_CHRG_OFF_BALANCE_AMT),
215          SUM(AT_MDS_FC_CLHE_BKRPY.TOTAL_PAST_DUE_AMT)
216        FROM
217          PBKDMDB.ADMIN.M_MDS_LK_UG_CLAIM_TYPE  AT_BANKRUPCY_CLAIM_TYPE RIGHT OUTER JOIN PBKDMDB.ADMIN.M_MDS_FC_CLHE_BKRPY
217      ! AT_MDS_FC_CLHE_BKRPY ON
218        
219        (AT_BANKRUPCY_CLAIM_TYPE.USER_INPUT_UG_CLAIM_TYPE_SK=AT_MDS_FC_CLHE_BKRPY.USER_INPUT_UG_CLAIM_TYPE_SK)
220           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_COLLECTION_STATUS  AT_BK__BOC_COLLECTION_STATUS ON
221        
7                                                          The SAS System                         10:10 Wednesday, November 29, 2017

222        (AT_MDS_FC_CLHE_BKRPY.CACS_BOC_COLLECTION_STATUS_SK=AT_BK__BOC_COLLECTION_STATUS.COLLECTION_STATUS_SK)
223           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_COLLECTION_STATUS  AT_BK_CAS_COLLECTION_STATUS ON
224        
225        (AT_MDS_FC_CLHE_BKRPY.CACS_COLLECTION_STATUS_SK=AT_BK_CAS_COLLECTION_STATUS.COLLECTION_STATUS_SK)
226           INNER JOIN (
227          SELECT
228          AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_SK,
229          AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_ID,
230          max(AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT_SK) as USAA_VENDOR_FILE_CREATION_SK,
231          max(AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT) as USAA_VENDOR_FILE_CREATION_DT
232        FROM
233          PBKDMDB.ADMIN.M_MDS_FC_CLHE_BKRPY  AT_MDS_FC_CLHE_BKRPY
234         WHERE
235        	CASE
236        		WHEN {d '2017-11-27'}= '2000-01-01' THEN USAA_VENDOR_FILE_CREATION_DT <  current_date-1
237          		ELSE USAA_VENDOR_FILE_CREATION_DT  <  {d '2017-11-27'}
238          	END
239        GROUP BY
240          AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_SK,
241          AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_ID
242          )  DT_Last_Occurence ON (DT_Last_Occurence.PRODUCT_ARRANGEMENT_SK = AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_SK  AND
242      ! DT_Last_Occurence.USAA_VENDOR_FILE_CREATION_SK =
243        
244        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT_SK)
245           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_DELINQUENCY_BUCKET  AT_BK_DM_DELINQUENCY_BUCKET ON
246        
247        (AT_MDS_FC_CLHE_BKRPY.DELINQUENCY_BUCKET_SK=AT_BK_DM_DELINQUENCY_BUCKET.DELINQUENCY_BUCKET_SK)
248           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_CACS_CUSTOMER  AT_BK_CACS_CUSTOMER ON
248      ! (AT_MDS_FC_CLHE_BKRPY.CACS_CUSTOMER_SK=AT_BK_CACS_CUSTOMER.CACS_CUSTOMER_SK)
249           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_LEGAL_STATUS  AT_BANKRUPTCY_LEGAL_STATUS ON
249      ! (AT_BANKRUPTCY_LEGAL_STATUS.LEGAL_STATUS_SK=AT_MDS_FC_CLHE_BKRPY.LEGAL_STATUS_SK and
250        
251        AT_BANKRUPTCY_LEGAL_STATUS.LEGAL_STATUS_TYPE_CD= 'CLHE')
252           INNER JOIN PBKDMDB.ADMIN.I_UTL_DM_DATE  AT_BK_DM_DATE ON
252      ! (AT_BK_DM_DATE.DATE_SK=AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT_SK)
253           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_PRODUCT_TYPE  AT_BANKTUPTCY_PRODUCT_TYPE ON
253      ! (AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_TYPE_SK=AT_MDS_FC_CLHE_BKRPY.PRODUCT_TYPE_SK)
254           INNER JOIN PBKDMDB.ADMIN.M_MDS_DM_PORTFOLIO  AT_BANKRUPTCY_PORTFOLIO ON
254      ! (AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_SK=AT_MDS_FC_CLHE_BKRPY.PORTFOLIO_SK)
255        
256        GROUP BY
257          AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_ID,
258          DT_Last_Occurence.USAA_VENDOR_FILE_CREATION_DT,
259          AT_MDS_FC_CLHE_BKRPY.AUTO_DR_IND,
260          AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT,
261          AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LEGAL_STATUS_CD,
262          AT_MDS_FC_CLHE_BKRPY.ACCOUNT_PROCESSING_STATUS_CD,
263          AT_BK_DM_DATE.CALENDAR_DT,
264          AT_BK_DM_DATE.CALENDAR_MONTH_CD,
265          AT_BK_DM_DATE.CALENDAR_MONTH_DC,
266          AT_BK_DM_DATE.CALENDAR_YEAR_NR,
267          AT_MDS_FC_CLHE_BKRPY.CF_CR_SCORE,
268          AT_MDS_FC_CLHE_BKRPY.CACS_ACCT_CHARGEOFF_IND,
269          AT_MDS_FC_CLHE_BKRPY.CACS_BANKRPTCY_IND,
270          AT_MDS_FC_CLHE_BKRPY.FIRST_ALS_BKPY_SEEN_DT,
271          AT_MDS_FC_CLHE_BKRPY.CACS_BOC_COLLECTION_STATUS_CD,
272          AT_MDS_FC_CLHE_BKRPY.CACS_LAST_ACTIVITY_DT,
273          AT_MDS_FC_CLHE_BKRPY.CACS_COLLECTION_STATUS_CD,
8                                                          The SAS System                         10:10 Wednesday, November 29, 2017

274          AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD1,
275          AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD3,
276          AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD4,
277          AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_CD2,
278          AT_MDS_FC_CLHE_BKRPY.CDS_BK_CONVERSION_DT,
279          AT_MDS_FC_CLHE_BKRPY.CDS_DISMISSAL_DT,
280          AT_MDS_FC_CLHE_BKRPY.CDS_DOCKET_DATE,
281          AT_MDS_FC_CLHE_BKRPY.CDS_DISCHARGE_DT,
282          AT_MDS_FC_CLHE_BKRPY.CDS_CHAPTER,
283          AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE,
284          AT_MDS_FC_CLHE_BKRPY.CDS_HEARING_DATE,
285          AT_MDS_FC_CLHE_BKRPY.CDS_REAFFIRMATION_DT,
286          AT_MDS_FC_CLHE_BKRPY.CDS_WITHDRAWAL_DT,
287          AT_MDS_FC_CLHE_BKRPY.CDS_SECONDARY_MEMBER_NUM,
288          AT_MDS_FC_CLHE_BKRPY.COLLATERAL_YEAR_NR1,
289          AT_MDS_FC_CLHE_BKRPY.COLLATERAL_DESCRIPTION_TXT1,
290          AT_MDS_FC_CLHE_BKRPY.COLLATERAL_MAKE_TXT1,
291          AT_MDS_FC_CLHE_BKRPY.COLLATERAL_MODEL_TXT1,
292          AT_MDS_FC_CLHE_BKRPY.COLLATERAL_SHORT_DESC_TXT1,
293          AT_MDS_FC_CLHE_BKRPY.COLLATERAL_REFERENCE_ID1,
294          AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND,
295          AT_MDS_FC_CLHE_BKRPY.CUSTOMER_LOAN_TYPE_CD,
296          AT_MDS_FC_CLHE_BKRPY.CHECKSUM_NR,
297          extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01'
298        THEN  current_date-1
299         ELSE  {d '2017-11-27'}  END
300         )- ( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT ) ),
301          extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01'
302        THEN  current_date-1
303         ELSE  {d '2017-11-27'}  END
304         )- ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) ),
305          AT_MDS_FC_CLHE_BKRPY.DERIVED_EXCEPTION_IND,
306          AT_MDS_FC_CLHE_BKRPY.FIRST_LOAN_PAYMENT_DUE_DT,
307          AT_MDS_FC_CLHE_BKRPY.HOME_EQUITY_FILE_LOCATION_TXT,
308          AT_MDS_FC_CLHE_BKRPY.LOAN_OLDEST_PAYMENT_DUE_DT,
309          AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD,
310          AT_MDS_FC_CLHE_BKRPY.LOAN_INTEREST_RATE_PCT,
311          AT_MDS_FC_CLHE_BKRPY.LIFE_CYCLE_STATUS_CD,
312          AT_MDS_FC_CLHE_BKRPY.ORIGINAL_PLANNED_END_DT,
313          AT_MDS_FC_CLHE_BKRPY.ORIGINAL_EFFECTIVE_DT,
314          AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY,
315          AT_MDS_FC_CLHE_BKRPY.PROCESSING_STATUS_SET_DT,
316          AT_MDS_FC_CLHE_BKRPY.PRODUCT_ARRANGEMENT_SK,
317          AT_MDS_FC_CLHE_BKRPY.STOP_ADVANCE_EXIST_IND,
318          AT_MDS_FC_CLHE_BKRPY.SECOND_LEV_LOAN_HIR_BRANCH_CD,
319          AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_ID_TYPE_CD,
320          AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_PD_ID,
321          AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_PD_ID_SEC,
322          AT_MDS_FC_CLHE_BKRPY.USAA_PARTY_PD_TYPE_CD_SEC,
323          AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT,
324          AT_MDS_FC_CLHE_BKRPY.WORKOUT_CODE_3_TXT,
325          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_TYPE_DC,
326          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_TYPE_CD,
327          AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_CHAPTER_CD,
328          AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_CHAPTER_DC,
329          AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND,
330          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_STATUS_CD,
331          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_STATUS_DESC_TXT,
9                                                          The SAS System                         10:10 Wednesday, November 29, 2017

332          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_AREA_CD,
333          AT_BK__BOC_COLLECTION_STATUS.COLLECTION_AREA_DESC_TXT,
334          AT_BK__BOC_COLLECTION_STATUS.MAR_ELIGIBLE_IND,
335          AT_BK__BOC_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_CD,
336          AT_BK__BOC_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_GROUP_CD,
337          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_STATUS_CD,
338          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_STATUS_DESC_TXT,
339          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_AREA_CD,
340          AT_BK_CAS_COLLECTION_STATUS.COLLECTION_AREA_DESC_TXT,
341          AT_BK_CAS_COLLECTION_STATUS.MAR_ELIGIBLE_IND,
342          AT_BK_CAS_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_CD,
343          AT_BK_CAS_COLLECTION_STATUS.CACS_AGENCY_CATEGORY_GROUP_CD,
344          last_day(( AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )),
345          AT_BK_CACS_CUSTOMER.CUST_FIRST_NM,
346          AT_BK_CACS_CUSTOMER.CUST_LAST_NM,
347          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_STREET_1_TXT,
348          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_CITY_NM,
349          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_STATE_CD,
350          AT_BK_CACS_CUSTOMER.CUSTOMER_ADDRESS_ZIP_CD,
351          AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD,
352          AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_DC,
353          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_ROLLUP_CD,
354          AT_BANKTUPTCY_PRODUCT_TYPE.PRODUCT_ROLLUP_DC,
355          ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
355      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
356        
357        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ),
358          extract ( epoch from ( last_day(( AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
358      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ),
359          case when  ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY ) >60 then 1 else 0 end,
360          AT_MDS_FC_CLHE_BKRPY.CDS_CUST_INFO_PRI_CD,
361          case when ( AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then 1 else 0 end,
362          AT_MDS_FC_CLHE_BKRPY.ACCOUNT_AUTO_DRAFT_DT,
363          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when ( AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then
363      !  1 else 0 end )=1 then 1 when  (
364        
365        AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102  and ( AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND )=1 then 1 else 0 end,
366          case when ( extract ( epoch from ( CASE WHEN {d '2017-11-27'}='2000-01-01'
367        THEN  current_date-1
368         ELSE  {d '2017-11-27'}  END
369         )- ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) ) )>60 then 1 else 0 end,
370          case when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END,
371          case when  ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY ) >0 then 0 else 1 end,
372          AT_BANKRUPTCY_LEGAL_STATUS.LEGAL_STATUS_DC,
373          case when( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_PROCESSING_STATUS_CD ) IN ('CF','PC')   then (
373      ! AT_MDS_FC_CLHE_BKRPY.PROCESSING_STATUS_SET_DT ) end,
374          case when ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) is null and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=1 then 1
375        when ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE )>(CASE WHEN {d '2017-11-27'}='2000-01-01'
376        THEN  current_date-1
377         ELSE  {d '2017-11-27'}  END) then 1 else 0 end,
378          AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND,
379          case when ( AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=1 and ( case
379      ! when
380        
381        AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END ) =1 and ( case when (
381      ! AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when (
382        
383        AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then 1 else 0 end )=1 then 1 when  ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102
10                                                         The SAS System                         10:10 Wednesday, November 29, 2017

383      !   and ( AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND )=1 then
384        
385        1 else 0 end )=0 and ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) is not null then 1 else 0 end,
386           case when ( AT_BK_CAS_COLLECTION_STATUS.COLLECTION_AREA_CD )='Z' and ((
386      ! substr(AT_MDS_FC_CLHE_BKRPY.CACS_COLLECTION_STATUS_CD,2,2)
387         ) between '10 ' and '99') and ( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_PROCESSING_STATUS_CD ) NOT IN (' CF','PC') and ( extract (
387      ! epoch from last_day(( CASE WHEN {d '2017-11-27'}
388        
389        ='2000-01-01'
390        THEN  current_date-1
391         ELSE  {d '2017-11-27'}  END
392         ))- ( AT_MDS_FC_CLHE_BKRPY.ACCOUNT_LAST_PAYMENT_DT ) ) )>60 and ( extract ( epoch from last_day(( CASE WHEN {d
392      ! '2017-11-27'}='2000-01-01'
393        THEN  current_date-1
394         ELSE  {d '2017-11-27'}  END
395         ))- ( AT_MDS_FC_CLHE_BKRPY.CDS_FILING_DATE ) ) ) >60  and AT_MDS_FC_CLHE_BKRPY.PCL_CUR_CHRG_OFF_BALANCE_AMT=0 and
395      ! AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 then
396        
397        1 else 0 end,
398          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10103 and ( AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( case
398      ! when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT
399        
400        >0 THEN 1 ELSE 0 END )=1 and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=0 and ( (
400      ! AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
401        
402        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- ( AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ) )>=120
402      ! then 1 else 0 end,
403          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( case
403      ! when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT
404        
405        >0 THEN 1 ELSE 0 END )=1 and ( AT_BANKRUPTCY_LEGAL_STATUS.BANKRAPTCY_IND )=0 and ( case when (
405      ! AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when (
406        
407        AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S' then 1 else 0 end )=1 then 1 when  ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102
407      !   and ( AT_BANKRUPCY_CLAIM_TYPE.ACTIVE_IND )=1 then
408        
409        1 else 0 end )=0 and ( ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
409      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
410        
411        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ) )>=1280 then 1 else 0 end,
412          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102 and ( case when
412      ! AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END )=1  and (
413        
414        AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )>=60 and ( AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY ) <=120 and ( case
414      ! when
415        
416        AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END )>=2500 then 1 else 0 end,
417          AT_BANKTUPTCY_PRODUCT_TYPE.RT_ACCOUNT_TYPE_DC,
418          AT_BANKTUPTCY_PRODUCT_TYPE.RT_ACCOUNT_TYPE_CD,
419          case when ( AT_BANKRUPTCY_PORTFOLIO.PORTFOLIO_CD )=10102  and ( case when ( AT_MDS_FC_CLHE_BKRPY.LOAN_POOL_CD )='S'
419      ! then 1 else 0 end )=0 and (
420        
421        AT_BANKRUPTCY_LEGAL_STATUS.LEGAL_STATUS_CD ) in ('11', '13', '1A', '1D', '1P', '1S', '2A', '2D', '2P', '2S', '3A', '3D',
421      ! '3P', '3S', '7A', '7D', '7P', '7S','3M','3U','3V',
422        
423        '7L','7V','CN','EL') and ( case when  AT_MDS_FC_CLHE_BKRPY.CURRENT_ACCOUNT_BALANCE_AMT >0 THEN 1 ELSE 0 END )=1 and (
423      ! AT_MDS_FC_CLHE_BKRPY.CHARGE_OFF_IND )='N' and ( (
424        
11                                                         The SAS System                         10:10 Wednesday, November 29, 2017

425        AT_MDS_FC_CLHE_BKRPY.OLDEST_OVERDUE_DAY_QTY )+( extract ( epoch from ( last_day((
425      ! AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT )) )- (
426        
427        AT_MDS_FC_CLHE_BKRPY.USAA_VENDOR_FILE_CREATION_DT ) ) ) )>=180 then 1 else 0 end,
428          AT_BANKRUPCY_CLAIM_TYPE.USER_INPUT_UG_CLAIM_TYPE_DC,
429          AT_BK_DM_DELINQUENCY_BUCKET.BUCKET_DESCRIPTION_TXT,
430          AT_BK_DM_DELINQUENCY_BUCKET.DELINQUENCY_BUCKET_NR
431        
432        
433        ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
434        QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds
      
435        
436        
437        GOPTIONS NOACCESSIBLE;
438        %LET _CLIENTTASKLABEL=;
439        %LET _CLIENTPROCESSFLOWNAME=;
440        %LET _CLIENTPROJECTPATH=;
441        %LET _CLIENTPROJECTPATHHOST=;
442        %LET _CLIENTPROJECTNAME=;
443        %LET _SASPROGRAMFILE=;
444        %LET _SASPROGRAMFILEHOST=;
445        
446        ;*';*";*/;quit;run;
447        ODS _ALL_ CLOSE;
448        
449        
450        QUIT; RUN;
451        
ballardw
Super User

You have two immediate and recurring errors.

The 22-322 is use of "FROM". In this case SAS sees Epoch as a variable name and is expecting an operator, comparison or membership in a list involving FROM as another variable.

The 200-322 is the use of { which I mentioned previously.

 

You might describe what that bit of code is supposed to do. It looks like you are pulling something from a date value but SAS does not recognize epoch and I'm not sure what it would be. 

 

Reeza
Super User

SAS CASE is not structured like that, its:

 

proc sql;
create table test as
select *, case when age<13 then 'child'
                      when age<15 then 'teen'
                      else 'adult' END as age_category
from sashelp.class;
quit;

And date literals are specified as:

 

'01Jun2017'd
F23D
Calcite | Level 5

Thank you for you, Reeza and ballardw!  You both have been very helpful and it is very much appreciated!!!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 27803 views
  • 0 likes
  • 3 in conversation