Sas datetime Conversion incorrect

Reply
Super Contributor
Posts: 418

Sas datetime Conversion incorrect

Hello everyone.   I have noticed that SAS actually incorrect converts datetimes pulled from a database system.  Has anyone else ever noticed this, and what is your solution for this problem?

What I mean is as follows:  I have a sql server database that has a "datetime" field.  In it, I have a record with a  time of "2014-07-09 19:18:58.597"..

If I write a pass through query on this data to pull it into sas, such as follows.

proc sql;
connect to odbc as myodbc (DSN = odbc_MYODBCNAME);
create table DateTimeBrokenTest as
select * from connection to myodbc
(SELECT *

from InvalidDateTable);
quit;

then the column is pulled in as a "DATETIME20." informat and Datetime20. format.   It looks like "09JUL2014:19:18:59" in SAS.  However if I format it as "datetime22.3" it looks like "2014-07-09 19:18:58.597" (similar to how it does in the database)... HOWEVER if I insert this record back into the sql table, I actually get a result of "2014-07-09 19:18:58.593".

So SAS has just effectively changed my datetime by .004 seconds (it's always .004 seconds).   I am assuming that maybe Sas is incorrectly guessing at the informat requested, however how can I be sure of this?  Can I change the informat in the sql pass through for this column?

Thanks a bunch, and let me know if my question doesn't make sense to anyone!

Super User
Super User
Posts: 6,502

Re: Sas datetime Conversion incorrect

Did you trying using a connection method that is specific to the database you are using instead of using generic ODBC connection?  I suspect that SAS would be better able to understand how to load timestamps when it knows what type of database engine you are using.  Make sure you have the proper SAS/ACCESS product licensed.

connect to ORACLE as myodbc

or

connect to TERADATA as myodbc

or

.....

Super Contributor
Posts: 418

Re: Sas datetime Conversion incorrect

We only have Base Sas. how would I check to see if I have SAS/ACCESS.. Also if I was connecting to Sql server, what would the 'connect to XXXX' be by chance?

Would it ber Connect to 'SqlServer'?

Super User
Super User
Posts: 6,502

Re: Sas datetime Conversion incorrect

Did you try using the SASDATEFMT dataset option?

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

Ask a Question
Discussion stats
  • 3 replies
  • 243 views
  • 0 likes
  • 2 in conversation