DATA Step, Macro, Functions and more

Oracle date into sas date

Reply
Occasional Contributor
Posts: 15

Oracle date into sas date

Hi,

I copied a file from Oracle to SAS.
There is a date field in the data set which is a numeric field with format and informat 23.

I tried checking like '01OCT2009'D but this doest work.
Please let me know if there is any way to convert this field into sas date.
Valued Guide
Posts: 632

Re: Oracle date into sas date

The conversion should have been made for you if the Oracle date was identified as such and you used PROC IMPORT or the ORACLE engine.

I think that Oracle bases its dates on 1jan1900. It is possible therefore that your date is 60 years worth of days too big. if this is the case the correction is:

date = date+'01jan1900'd;
Valued Guide
Posts: 2,175

Re: Oracle date into sas date

the proper solution depends on "how" you copied that data.
However, try a date-time format like datetime, or (if you expected only date information) DTDATE.

most of the database interfaces to SAS collect date information as a datetime value
Respected Advisor
Posts: 3,908

Re: Oracle date into sas date

Hi

Unlike SAS Oracle has a lot of different field types.

If the field type in Oracle is date or datetime then it should translate correctly to a SAS datetime format.

What I've seen already is that people store a date in a numeric field (as a number and not as a date), let's say 20100618.
If this is the case then SAS can't know that this field should be interpreted as a date but reads it simply as a number. If you want to convert such a field to a date or datetime then you have to either do this in SAS (with format, informat) or if you're using pass through SQL you can also convert the field already in Oracle using the appropriate formats there.

I believe to remember that in Oracle dates start from 1January1970.

HTH
Patrick
Ask a Question
Discussion stats
  • 3 replies
  • 196 views
  • 0 likes
  • 4 in conversation