BookmarkSubscribeRSS Feed
Jacey
Calcite | Level 5

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

 

 

 

1 REPLY 1
SuryaKiran
Meteorite | Level 14

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

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2913 views
  • 2 likes
  • 2 in conversation