topic Re: DATETIME20 Format error on where clause in SAS Programming

DATETIME20 Format error on where clause

anonymous_user — Mon, 20 Mar 2017 10:48:29 GMT

Hi,

When I run my SAS code with this where clause, my SAS errors

AND XICQ_SUBACCOUNT_SUMMARY.XICQ_COMPLETION_DATE>={ts '2013-01-01 00:00:01'}

The variable format XICQ_COMPLETION_DATE DATETIME20. and the output is like

13AUG2004:00:00:00

21MAY2004:00:00:00

16AUG2004:00:00:00

03JUN2004:00:00:00

06MAY2004:00:00:00

Any idea how I can get this part of my where clause to work please?

Thanks

Re: DATETIME20 Format error on where clause

andreas_lds — Mon, 20 Mar 2017 12:19:27 GMT

using a proepr datetime constant will most likely solve the issue, but because you have not posted the error message, this is just guessed.

Re: DATETIME20 Format error on where clause

Kurt_Bremser — Mon, 20 Mar 2017 12:34:46 GMT

{ts '2013-01-01 00:00:01'}

is not a proper SAS datetime literal.

The correct SAS notation would be

'01jan2013:00:00:01'dt

or you could use

input('2013-01-01T00:00:01',e8601dt.)

instead

Re: DATETIME20 Format error on where clause

anonymous_user — Mon, 20 Mar 2017 13:31:01 GMT

This is my error message -

ERROR: ORACLE prepare error: ORA-00911: invalid character. SQL statement: SELECT ACCOUNTS.ACC_ACCOUNT_NO,
ACCOUNTS.ACC_PURPOSE_CODE, ACCOUNT_TYPES.ACT_LEVEL1_CODE, XICQ_SUBACCOUNT_SUMMARY.XICQ_COMPLETION_DATE, MAM34.CLIINCAMT,
MAM34.CLIINCFREQ, ACCOUNTS.ACC_ACTUAL_ADVANCE, MAM34.CLIINCTYP, MAM34.CLIINCSQ, MAM34.APPLSEQ, ACCOUNTS.ACC_SUBACC_NO,
MAM34.CLINUM FROM ACCOUNTS LEFT OUTER JOIN ACCOUNT_TYPES ON ACCOUNTS.ACC_ACT_CODE=ACCOUNT_TYPES.ACT_CODE LEFT OUTER JOIN
XICQ_SUBACCOUNT_SUMMARY ON (ACCOUNTS.ACC_ACCOUNT_NO=XICQ_SUBACCOUNT_SUMMARY.XICQ_ACCOUNT_NO) AND
(ACC_SUBACC_NO=XICQ_SUBACCOUNT_SUMMARY.XICQ_SUBACC_NO) LEFT OUTER JOIN MAM34 ON
XICQ_SUBACCOUNT_SUMMARY.XICQ_ACCOUNT_NO=MAM34.MORTGAGE WHERE MAM34.CLIINCTYP='REN' AND ACCOUNT_TYPES.ACT_LEVEL1_CODE='7' AND
ACCOUNTS.ACC_PURPOSE_CODE<>'FAD' AND XICQ_SUBACCOUNT_SUMMARY.XICQ_COMPLETION_DATE>={ts '2013-01-01 00:00:01'}.

I tried using these ut still didn't work

'01jan2013:00:00:01'dt

or you could use

input('2013-01-01T00:00:01',e8601dt.)

Re: DATETIME20 Format error on where clause

Kurt_Bremser — Mon, 20 Mar 2017 13:36:45 GMT

So you have an Oracle problem, not a SAS problem. Ask your Oracle people for assistance how to provide a timestamp literal, or consult the Oracle documentation.

I guess you should use

TIMESTAMP '2013-01-01 00:00:01'

instead of

{ts '2013-01-01 00:00:01'}

Re: DATETIME20 Format error on where clause

Sven111 — Mon, 20 Mar 2017 16:27:17 GMT

You need to be careful with DATE/TIMESTAMP's when moving between Oracle and SAS. I don't recall exactly which off the top of my head, but I know that when moving between the two SAS interprets Oracle DATES or TIMESTAMPS as either SAS DATETIMES or CHAR's, I think there is also a difference between TIMESTAMP and TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE, in whether SAS will treat it as a DATETIME or CHAR. Also, Oracle does not have a concept of a pure date, even the DATE type includes a time component.

My guess is that this type mismatch is causing your problems.