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
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. ...
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. ...
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;
please provide date sample that appear in oracle...
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
Thank you all for your help! I really appreciate it! Nice and simple solution. Thanks Reeza!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: