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
using a proepr datetime constant will most likely solve the issue, but because you have not posted the error message, this is just guessed.
{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.)
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'}
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.