topic Re: Having trouble with date formatting when importing from Oracle Database using Proc SQL in SAS Procedures

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

teg_76 — Wed, 31 Jul 2013 03:12:37 GMT

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

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

Reeza — Wed, 31 Jul 2013 04:40:25 GMT

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. ...

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

Tushar — Wed, 31 Jul 2013 04:50:32 GMT

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;

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

sugeshnambiar — Wed, 31 Jul 2013 05:44:30 GMT

please provide date sample that appear in oracle...

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

jakarman — Wed, 31 Jul 2013 07:37:04 GMT

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

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

teg_76 — Wed, 31 Jul 2013 15:13:42 GMT

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