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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1017 views
  • 0 likes
  • 4 in conversation