topic Re: Wrong Date inserting in oracle table from SAS in SAS Procedures

Wrong Date inserting in oracle table from SAS

RDS2020 — Mon, 20 Jul 2020 03:32:47 GMT

Hi All,

I am using simple code to insert date field into oracle date field and it is inserting as 01JAN1960.

All other fields are inserging fine other than Date field which is inserting as 01jan1960 for all rows.

INSERT into oracle_table

Select Date_column from WORK.DATA D ;
QUIT;

Why so? how can we solve this.

Regards,

RDS

Re: Wrong Date inserting in oracle table from SAS

SASKiwi — Mon, 20 Jul 2020 04:16:14 GMT

Most likely your Oracle date column is in fact a datetime. Check the Oracle table schema to confirm. If so then convert your SAS date into a datetime using the DHMS function before loading into Oracle: DHMS

Re: Wrong Date inserting in oracle table from SAS

RDS2020 — Mon, 20 Jul 2020 04:23:38 GMT

Hi,

Thanks for reverting. I have checked both Oracle and SAS both are in DATE format. Also i have tried changing Oracle in timestamp thinking it might work but it is still not working.

Regards,

RDS

Re: Wrong Date inserting in oracle table from SAS

SASKiwi — Mon, 20 Jul 2020 04:44:28 GMT

How is your date variable defined in SAS? It must be defined as a numeric variable with a permanent SAS format assigned to it like DATE9. :

data want;
 format Date_Var date9.;
  Date_Var = '20Jul2020'd;
run;

Re: Wrong Date inserting in oracle table from SAS

RDS2020 — Mon, 20 Jul 2020 04:52:51 GMT

Yes in sas it is defined as
TYPE:- Numeric
FORMAT:- Date9.

Re: Wrong Date inserting in oracle table from SAS

SASKiwi — Mon, 20 Jul 2020 05:00:52 GMT

Please post the SAS log of your Oracle load program.

Re: Wrong Date inserting in oracle table from SAS

RDS2020 — Mon, 20 Jul 2020 05:05:26 GMT

Re: Wrong Date inserting in oracle table from SAS

Patrick — Mon, 20 Jul 2020 13:50:09 GMT

An Oracle data type of DATE actually stores DateTime values without fractional seconds. TimeStamp is the same but also stores fractional seconds.

A SAS Date value is the count of Days since 1/1/1960, a SAS DateTime value is the count of Seconds since 1/1/1960. The values are stored in a SAS Numerical variable and only the format applied will instruct SAS how to treat this numerical value (like how to pass it to Oracle).

I would have thought that SAS gets it right with a Date9. format but from what you describe it doesn't. May be worth raising with SAS TechSupport as that's not behaving like documented here.

You could try the following:

INSERT into oracle_table
  select date_column from
  (
    select Date_column*86400 as date_column format=datetime20. from WORK.DATA D 
  );
QUIT;

Or just a data step before the Insert where you convert the SAS Date value to a SAS DateTime value by multiplying it by the seconds of a day (and also apply format Datetime20.).

Re: Wrong Date inserting in oracle table from SAS

RDS2020 — Mon, 20 Jul 2020 23:08:23 GMT

Adding this to the Date column solves the problem
"*86400 as date_column format=datetime20."

Weird solution but yes a solution

Re: Wrong Date inserting in oracle table from SAS

SASKiwi — Mon, 20 Jul 2020 23:46:01 GMT

@RDS2020 - Converting your SAS date to a datetime using the DHMS function as in the link I provided would also work. IMO using SAS date functions produces more easily explainable / understandable code.

 select DHMS(Date_column, 0, 0, 0) as date_column format=datetime20.