BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ganeshk
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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)

View solution in original post

16 REPLIES 16
Ganeshk
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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
Super User

@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
Obsidian | Level 7

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

Ganeshk
Obsidian | Level 7

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
Opal | Level 21
sasdt=dhms(day, 0, 0, time);
format sasdt datetime21.;
Kurt_Bremser
Super User

@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
Obsidian | Level 7

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
Super User

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

 

 

Ganeshk
Obsidian | Level 7

Thanks Reeza. Actually this result of date is perfect.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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