topic Oracle date into sas date in SAS Programming

Oracle date into sas date

SASACC — Fri, 18 Jun 2010 06:07:20 GMT

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.

Re: Oracle date into sas date

ArtC — Fri, 18 Jun 2010 06:40:50 GMT

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;

Re: Oracle date into sas date

Peter_C — Fri, 18 Jun 2010 07:37:24 GMT

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

Re: Oracle date into sas date

Patrick — Fri, 18 Jun 2010 12:20:56 GMT

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