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)
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?
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;
Yes, I want to create the macro variable in sas equivalent to Oracle TIMESTAMP(6) format.
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?
It's not working fine.. here is an error.
ERROR: ORACLE PREPARE ERROR: ORA-00933: SQL command not properly ended. SQL STATEMENT
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.
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.
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.
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;
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'
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.