Having trouble with date formatting when importing from Oracle Database using Proc SQL

[teg_76]

I'm using Proc SQL to query an oracle database.  I have a date that comes through as a datetime20.  When I convert it to a SAS data, the numeric value of that data is 10 digits long.  Given that the dates are all in the past few years, the numeric value should be around 18000 (ex 4/13/2009 minus 1/1/1960 is 18000).  How do I get the date from Oracle to accurately be reflected in SAS?  My code is at work (I'm at home now), but it generally looks like this:

PROC SQL;

     CREATE TABLE APPT AS

     (SELECT A.DATE FORMAT datew. FROM LIB.APPOINTMENT);

QUIT;

An example value in the field "Appointment" is 1080844400'.  Why is it 10 digits long?  The actual dates are recent, so should they all be around five digits?

Thanks in advance.

Tom

[Reeza · Accepted solution]

You have the date in datetime still, so its really in seconds I think. You can use the datepart function to get only the date.

select datepart(a.date) as date format date9. ...

[Tushar]

Or you can provide your own format irrespective whatever date format you are reading, like use anydtdte. informat

ex,

data want;

  input date anydtdte.;

  format date date9.;

  cards;

28MAY1927:00:00:00

;

run;

[sugeshnambiar]

please provide date sample that appear in oracle...

[jakarman]

Please review: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition (Data Types for Oracle)

Oracle is using datetime (char) as date fields. Conversions SAS to Oracel vice/versa must support this conversion. Give the needed addtional information to all procedures and steps including formats (SAS).

SAS is using datetime as equivalent being the number of seconds since 1 jan 1960.

DBTYPE and DBSASTYPE can help in overwrite automatic conversions 

[teg_76]

Thank you all for your help!  I really appreciate it!  Nice and simple solution.  Thanks Reeza!