How to convert the datetime from oracle when coding SAS?

Reply
Contributor
Posts: 31

How to convert the datetime from oracle when coding SAS?

Hello!

When I run the code as below, I will get a table containing three variables. No1 is char_datetime, a character variable, No2 is date_datetime, a date variable, and No3 date is also a date variable. But I have to used oracle function, such as to_number, or to_date in order to get a date format like 03/31/2018 or Mar 31, 2018. Do u know how to apply? Also, do u know how to convert the variable Char_datetime from character into number with to_number function using the information below? Thanks!

 

proc sql;

CONNECT TO teradata as database (user= password= tdpid= mode=);

create table want as select * from connection to database

 (Select   char_datetime, date_datetime,date );quit;

 

 

char_datetimedate_datetime date
125358025FEB2018:00:00:00.00011MAR2018
 121541025FEB2018:00:00:00.00014MAR2019
 181119025FEB2018:00:00:00.00019MAR2020
 171703018MAR2018:00:00:00.00021MAR2021
 180747018FEB2018:00:00:00.00031MAR2022
 211755025FEB2018:00:00:00.00011MAR2023
 121423025FEB2018:00:00:00.00011MAR2024
 120732025FEB2018:00:00:00.00011MAR2025

 

 

 

Valued Guide
Posts: 597

Re: How to convert the datetime from oracle when coding SAS?

[ Edited ]

Did you try Implicit way (Libname) instead of pass-through. Do you have a reason to stick with pass-through?

Note: Your are connecting to teradata so why do you need Oracle functions.

LIBNAME ter teradata  user= password= ;

proc sql;
create table want as 
select INPUT(char_datetime,$12.) as num_datetime,
		DATEPART(date_datetime) format=date9. as date_datetime,
		date format=date9. 
	from ter.table;
quit;
Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 1 reply
  • 137 views
  • 2 likes
  • 2 in conversation