SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Date format changing while processing SAS file to oracle Sql

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Date format changing while processing SAS file to oracle Sql

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


Accepted Solutions
Solution
‎10-06-2016 08:19 AM
Super User
Posts: 6,935

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

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 5,256

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

Contributor
Posts: 29

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

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

Solution
‎10-06-2016 08:19 AM
Super User
Posts: 6,935

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

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 29

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

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

Super User
Posts: 17,792

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

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

 

Super User
Posts: 6,935

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


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 29

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

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

Super User
Posts: 6,935

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

Contributor
Posts: 29

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

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
Respected Advisor
Posts: 3,887

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

sasdt=dhms(day, 0, 0, time);
format sasdt datetime21.;
Super User
Posts: 6,935

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


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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 29

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

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

 

 

Super User
Posts: 17,792

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

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

 

 

Contributor
Posts: 29

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

Thanks Reeza. Actually this result of date is perfect.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 711 views
  • 4 likes
  • 5 in conversation