BookmarkSubscribeRSS Feed
Banu
Obsidian | Level 7

Hi,

 

I am trying to read one of the Teradata table DATE and DATETIME column values into SAS and then need to load into the ORACLE table DATE and DATETIMETSMAP columns.

 

I am trying as below.

 

proc sql;

connect to teradata(&myconnections.);

create table src1 as select * from connection to teradata (select dt1,dt_ts1 from tab1);

quit;

proc print data=src1;run;

 

dt1                              dt_ts1

16JUN2023              16JUN2023:06:41:05.655255

 

Also below are proc contents output.

 

#  Variable Type    Len   Format                  Informat

1    dt1            Num     8     DATE9.                  DATE9. 
2   dt_ts1       Num     8     DATETIME26.6   DATETIME26.6

 

Could you please help me how can I load above two date values into Oracle table with data types: DATE and TIMESTAMP(6). Thank you so much In advance.

3 REPLIES 3
Banu
Obsidian | Level 7
When I tried to insert data into oracle table I can values like 14JUN13. Loading date values with 10 years back. Please suggest if I need to use any formats.
Tom
Super User Tom
Super User

Example of what you tried.

 

It should work normally.

libname td teradata ..... ;
proc sql;
insert into td.td_table (date,datetime)
  select date,datetime 
  from saslib.sas_dataset
;
quit;
Patrick
Opal | Level 21

Have a read of Data Types for Oracle

Patrick_0-1686974243473.png

If you want full control what data types you get in the Oracle table - i.e. not just TIMESTAMP but TIMESTAMP(6) then use explicit SQL passthrough and create the Oracle table explicitly before loading into it.

 

According to the Oracle documentation the default for TIMESTAMP is TIMESTAMP(6) so you should be fine even if leaving the job to SAS.

The SAS docu states for automatic conversion "any date, time,....format...": In my experience with no more current releases of SAS things didn't work as expected with formats other than Date and DateTime. But may-be that's fixed now.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 3 replies
  • 293 views
  • 0 likes
  • 3 in conversation