DATA Step, Macro, Functions and more

DATETIME20 Format error on where clause

Reply
anonymous_user
Posts: 0

DATETIME20 Format error on where clause

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

 

 

 

 

Super Contributor
Posts: 345

Re: DATETIME20 Format error on where clause

Posted in reply to anonymous_user

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

Super User
Posts: 7,866

Re: DATETIME20 Format error on where clause

Posted in reply to anonymous_user
{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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
anonymous_user
Posts: 0

Re: DATETIME20 Format error on where clause

Posted in reply to KurtBremser

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

 

 

 

 

 

 

 

 

 

Super User
Posts: 7,866

Re: DATETIME20 Format error on where clause

Posted in reply to anonymous_user

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'}

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 83

Re: DATETIME20 Format error on where clause

Posted in reply to anonymous_user

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.

Ask a Question
Discussion stats
  • 5 replies
  • 186 views
  • 0 likes
  • 4 in conversation