topic Re: Date format changing while processing SAS file to oracle Sql in SAS Data Management

Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 09:37:33 GMT

Hi all,

I have a SAS data set with variable "Date" and having format mmddyy8.

when i set the SAS data set and create new data set on Oracle SQL my date is changing to 1/1/1960 15:00:00 [basically to datetime20. format].

How to fix this please help.

Thanks,

Ganesh K

Re: Date format changing while processing SAS file to oracle Sql

LinusH — Thu, 06 Oct 2016 10:27:18 GMT

Look in the documentation of data type mapping:

https://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p06jk0u30uhuj5n18fqw9sxr25lk.htm

Re: Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 10:36:19 GMT

Hi Linush,

Actually in SAS Data i have date 05/25/2016

When i create SQL data i got date 1/1/1960 15:30:00

My date format was changed. I used Format to make chages while creation of SQL data

Nothing is changing my date. so could you please guide

Thanks,

Ganesh K

Re: Date format changing while processing SAS file to oracle Sql

Kurt_Bremser — Thu, 06 Oct 2016 10:42:56 GMT

You need to use the DBTYPE= option.

To quote from the link that @LinusH gave you:

"To override these data types, use the DBTYPE= data set option during output processing."

From the symptoms, your date is stored as a datetime value in Oracle.

Another workaround would be to convert to datetime locally before you write to Oracle (just multiply the value by 86400, or use the dhms() function)

Re: Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 11:13:01 GMT

Hi kurtBremser,

Multiple by value 86400 worked for me. since i am using SAS 9.3 version. Is there any other method?

Thanks,

Ganesh K

Re: Date format changing while processing SAS file to oracle Sql

Reeza — Thu, 06 Oct 2016 11:18:01 GMT

Convert to datetime using DHMS function.

Ude DBTYPE option as indicated to have it handle conversion.

Example 3

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0v4ma1zb9lu99n1728j279rjcqi.htm

Re: Date format changing while processing SAS file to oracle Sql

Kurt_Bremser — Thu, 06 Oct 2016 11:18:56 GMT

@Ganeshk wrote:

Hi kurtBremser,

Multiple by value 86400 worked for me. since i am using SAS 9.3 version. Is there any other method?

Thanks,

Ganesh K

As I said, use the dhms() function.

Re: Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 11:20:37 GMT

Any example on dhms() function, how to use it?

Re: Date format changing while processing SAS file to oracle Sql

Kurt_Bremser — Thu, 06 Oct 2016 11:22:31 GMT

http://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p1b9d1kbo0czoxn1ouj1kcxwqzn1.htm

Re: Date format changing while processing SAS file to oracle Sql

Patrick — Thu, 06 Oct 2016 11:27:37 GMT

Assign a format of DATE9. to the SAS variable. I believe if that's done then the SAS/Access to Oracle engine gets it and converts your SAS date value correctly to a Oracle DATE value.

Re: Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 11:33:17 GMT

Actually i have a variable date in that case how to use?

sasdt=dhms(day, 0, 0, time);
put sasdt datetime.;

02APR03:15:05:02

Re: Date format changing while processing SAS file to oracle Sql

Patrick — Thu, 06 Oct 2016 11:36:40 GMT

sasdt=dhms(day, 0, 0, time);
format sasdt datetime21.;

Re: Date format changing while processing SAS file to oracle Sql

Kurt_Bremser — Thu, 06 Oct 2016 11:39:00 GMT

@Ganeshk wrote:

Actually i have a variable date in that case how to use?
sasdt=dhms(day, 0, 0, time);
put sasdt datetime.;
02APR03:15:05:02

The first parameter for the dhms() function is not a day, but a SAS date:

data _null_;
sasdt = dhms(date(),0,0,0);
format sasdt datetime19.;
put sasdt=;
run;

Log:

16         data _null_;
17         sasdt = dhms(date(),0,0,0);
18         format sasdt datetime19.;
19         put sasdt=;
20         run;

sasdt=06OCT2016:00:00:00

Re: Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 11:55:54 GMT

Thanks for quick response.

Here is my code:

Data oracle.Tab1;

Set Tab2;

Response_Date=Dhms(Date(),0,0,0);

Post_Date=Dhms(Date(),0,0,0);

Fromat Response_Date datetime19.;

Fromat Post_Date datetime19.;

Put Response_Date=;

Put Post_Date=;

run;

Gives todays date. What can be done to modify. My existing variable name are Response_Date and Post_Date.

Thanks

Re: Date format changing while processing SAS file to oracle Sql

Reeza — Thu, 06 Oct 2016 12:57:40 GMT

Have you tried replacing date() with your variable name for starters?

Re: Date format changing while processing SAS file to oracle Sql

Ganeshk — Thu, 06 Oct 2016 13:16:33 GMT

Thanks Reeza. Actually this result of date is perfect.

Re: Date format changing while processing SAS file to oracle Sql

Patrick — Fri, 07 Oct 2016 07:48:49 GMT

It's imho a flaw that the SAS/Access to Oracle engine doesn't recognize all the OOTB SAS Date and DateTime formats but if you use Date. and Datetime. then things will just work implicitely without any extra options.

I've just run below code which loads a SAS Date and DateTime value into Oracle (creating a new table in Oracle) and things just work.

data work.SAS_table;
  format SAS_Date_var date9. SAS_DateTime_var datetime21.;
  SAS_Date_var=today();
  SAS_DateTime_var=datetime();
run;

proc append data=work.SAS_table base=ora.Oracle_Table;
run;quit;

That's how the Oracle table then looks like from the SAS side:

And here from the Oracle side via SQL Developer