Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

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

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


Accepted Solutions
Solution
‎07-31-2013 12:40 AM
Super User
Posts: 19,822

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

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

View solution in original post


All Replies
Solution
‎07-31-2013 12:40 AM
Super User
Posts: 19,822

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

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

Frequent Contributor
Posts: 88

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

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;

Occasional Contributor
Posts: 14

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

please provide date sample that appear in oracle...

Trusted Advisor
Posts: 3,214

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

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 

---->-- ja karman --<-----
Contributor
Posts: 41

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1702 views
  • 6 likes
  • 5 in conversation