topic Re: Time Fields using SQL in SAS Procedures

Time Fields using SQL

Scottcom4 — Thu, 21 May 2009 23:56:12 GMT

Hi Guys,

I am linked to oracle tables and am attempting to retireve the contents of said table using SQL. My problem is that I can't get date/times to work. By saying this I mean that I recieve the following error whenever I attempt to access date/time data.

ERROR: Prepare error: ICommandPrepare::Prepare failed.
SQL statement SELECT
TransactionsTable.TransactionID,
TransactionsTable.CheckID,
TransactionsTable.UpdateTime

FROM
TransactionsTable

The below code is what I am attempting to run. Does anyone know how I can complete this task successfully.

Thank you for your help.

PROC SQL;
CONNECT TO oledb AS oradb
(User=XXXXXXX Password=XXXXXXX DataSource=XXXXXXXXXXXXXXX provider=MSDAORA.1 );;
CREATE table CmovTrans_Dat
AS SELECT * FROM connection to oradb
(SELECT
TransactionsTable.TransactionID,
TransactionsTable.CheckID,
TransactionsTable.UpdateTime

FROM
TransactionsTable
);
DISCONNECT FROM oradb;
run;

Re: Time Fields using SQL

sbb — Wed, 27 May 2009 21:12:21 GMT

It's not clear to me, but are you able to execute successfully with the most basic connection to your Oracle environment from SAS. Also, consider your SAS version and service pack maintenance level - I found several hits on the SAS support website, using the Google advanced search argument below:

proc sql oracle prepare error site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Time Fields using SQL

Scottcom4 — Thu, 28 May 2009 04:43:05 GMT

Hi Scott,

Yes you are correct in your understanding, I am able to access the Oracle tables using Proc SQL, however as soon as I add a time or date variable I begin to get errors. My work around for the date issue was to use to_char(Varname1), howeever this did not work for the time and date variables. I gather there is a better alternative to using to_char, however at this stage it works as desired.

Thank you for the google search criteria.

Regards,
Scott

Re: Time Fields using SQL

LinusH — Thu, 28 May 2009 06:22:08 GMT

Your example shows SQL pass-thru. What happends if you use LIBANME instead?

/Linus