BookmarkSubscribeRSS Feed

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

 

 

 

 

5 REPLIES 5
andreas_lds
Jade | Level 19

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

Kurt_Bremser
Super 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

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

 

 

 

 

 

 

 

 

 

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

 

Sven111
Pyrite | Level 9

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1847 views
  • 0 likes
  • 4 in conversation