Date format changing while processing SAS file to oracle Sql

[Ganeshk]

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

[LinusH]

Look in the documentation of data type mapping:

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

[Ganeshk]

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

[Kurt_Bremser · Accepted solution]

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)

[Ganeshk]

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

[Reeza]

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#n0v4ma1zb9lu99n172...

[Kurt_Bremser]

---

@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.

[Ganeshk]

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

[Kurt_Bremser]

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

[Ganeshk]

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

[Patrick]

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

[Kurt_Bremser]

---

@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

[Ganeshk]

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

[Reeza]

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

[Ganeshk]

Thanks Reeza. Actually this result of date is perfect.