Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Home
- /
- Programming
- /
- SAS Procedures
- /
- ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ...
Options
- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Calcite | Level 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 11-29-2017 11:50 AM
(27757 views)
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
Super User
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Calcite | Level 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Super User
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Super User
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Calcite | Level 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Discussion stats
-
5 replies
-
11-29-2017 11:50 AM
-
27758 views
-
0 likes
-
3 in conversation
-
