Oracle data extraction based on TIMESTAMP(6) format

[japsas100]

We are trying to extract data from Oracle based DateTime columns. In oracle, we have TIMESTAMP(6) format.

I need to create the same format in sas so that I can extract the oracle data based on DateTime format.

We read the data at sas level its showing data format 27.6 but I am not able to create the format at sas level to filter out Oracle data.

For Example
Oracle data definition
Col1 datatype(TIMESTAMP(6)) data values (19-Jul-17 11.12.46.983000000 AM)

[Tom]

Not sure what you question is.  

If you have a TIMESTAMP variable in Oracle then it will be converted to a number in SAS with the DATETIME format attached.

You can use datetime literals if you want to type a specific datetime value into your code.

where myvar > '01JAN2019:10:20.123456'dt

What does the (6) mean in TIMESTAMP(6)? Does that matter at all?

[japsas100]

Thanks for the quick reply.

I want to extract data from Oracle on the datetime column. In Oracle, DateTime column format is TIMESTAMP(6).

Here is my sample code to extract the data from Oracle and its work fine. my question is how I can define the ''03-MAY-17 01.15.08.214000000 PM'  format in sas to create the macro and pass in where condition while extracting the data from Oracle.

proc sql;
%oracle_connect(&rtt);
create table temp as
select * from connection to &tt
(
SELECT *

FROM schema.table
where TO_CHAR(CTETIME,'DD-MON-RR HH.MI.SS.FF9 AM')= '03-MAY-17 01.15.08.214000000 PM'
);
quit;

[Tom]

I still do not see what your question is.
Are you asking how to pass a datetime value via a macro VARIABLE into your Oracle code?

[japsas100]

Yes, I want to create the macro variable in sas equivalent to  Oracle  TIMESTAMP(6) format.

[Tom]

The tricky thing with passing values into remote database is that most require the use of single quotes around strings and in SAS the single quotes prevent the expansion of macro variables.  Here is one method to get the single quotes added.

%let dt=03MAY2017:13:15:08.214;
%let dt=%unquote(%bquote('&dt'));

Since your sample code is already using the Oracle function TO_CHAR() to convert the timestamp into a string why not just modify the format string you are passing to it to use a format that works better for strings formatted by SAS?

[japsas100]

It's not working fine.. here is an error.

ERROR: ORACLE PREPARE ERROR: ORA-00933: SQL command not properly ended. SQL STATEMENT

[Tom]

Show enough of the SAS log so we can see what code you tried and exactly what statements are causing the error.

Make sure to use the {i} button on the editor menu to open a pop to paste in the text. That will prevent the forum editor from mangling the formatting.

[japsas100]

Thanks for the reply.

My objective is to create the below format in sas dataset then pass the value in where clause.

'03-MAY-17 01.15.08.214000000 PM' : This data I copied from Oracle column

How I can create the above format in the sas data step.

[Tom]

Why would you want that particular format? It is missing the century part of the year. It is using am/pm instead of 24 hour clock.
If you really want to create that then look at using custom picture format.

[japsas100]

Hi,

I need to extract the data from Oracle based on DateTime .

Just want to check which is the approach to pull the data from Oracle based on below scenario.

Oracle table(Data_Type TIMESTAMP(6))

Column1

01-JUN-17 10.07.18.631000000 AM

We need to create the sas table by passing the DateTime value at sas level.  We need to include the milliseconds in time so that we will not extract the same data again.

[Tom]

It is probably easier to just let SAS do that for you by using implicit SQL instead.

For example you could create a libref pointing to your oracle table.

Also with fractional values (floating point numbers) I wouldn't try to use exact matching.

libname mydb oracle ..... ;
data temp;
  set mydb.table ;
  where ctetime between '03MAY2017:13:15:08'dt and '03MAY2017:13:15:09'dt ;
run;

[Tom]

Still not sure why you are using such a goofy format since you could just change the FORMAT string in Oracle so that your Oracle code could use a different character representation of the value.

Here is definition of a SAS format to display datatime values into that style.

proc format;
picture xxdt
  low-high = '%0d-%b-%0y %0I.%0M.%0s %p' (datatype=datetime)
;
run;

Note that SAS will only display 6 places for the fractional seconds.

639   %let dt=%sysfunc(datetime(),xxdt28.6);
640   %let qdt=%sysfunc(quote(&dt,%str(%')));
641   %put &=dt &=qdt;
DT=12-MAR-19 11.38.16.303000 AM QDT='12-MAR-19 11.38.16.303000 AM'