07-16-2014 11:12 AM
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.
connect to odbc as myodbc (DSN = odbc_MYODBCNAME);
create table DateTimeBrokenTest as
select * from connection to myodbc
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!
07-16-2014 12:27 PM
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
connect to TERADATA as myodbc
07-16-2014 12:32 PM
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'?