Help using Base SAS procedures

Time Fields using SQL

Reply
Contributor
Posts: 42

Time Fields using SQL

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:Smiley Tonguerepare 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;
Super Contributor
Super Contributor
Posts: 3,174

Re: Time Fields using SQL

Posted in reply to Scottcom4
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.
Contributor
Posts: 42

Re: Time Fields using SQL

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
Super User
Posts: 5,424

Re: Time Fields using SQL

Posted in reply to Scottcom4
Your example shows SQL pass-thru. What happends if you use LIBANME instead?

/Linus
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 118 views
  • 0 likes
  • 3 in conversation