02-28-2012 03:38 PM
I am looking for a SAS equivalent for the following:
is used to extract data based on the 1/2 hour window defined in the condition.
I need to define this in the WHERE caluse of an EXTRACT transformation in DIS job.
02-28-2012 03:55 PM
If TIMESTAMP is a SAS datetime value then the condition
intck("MINUTE", TIMESTAMP, DATETIME(), "CONTINUOUS") <= 30
will get the timestamps from the last half hour.
02-28-2012 04:12 PM
Thanks for the reply.
The column value is coming from Oracle that needs to be within 30 minutes time period. So based on the timestamp value from that column I need to get only those records which have a time stamp of last 30 minutes.
02-28-2012 08:59 PM
You don't tell us which DI version you're using.
I believe intck() is a SAS function which can't be passed to Oracle http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113612.htm
If so then better don't use it in a where clause when extracting data from Oracle as this would result in the whole table loaded from Oracle to SAS for processing.
What you could do:
Use DI's SQL join transformation and select the pass-through option, then simply use the already known Oracle syntax in the where clause.
I'm suggesting a SQL join transformation because I understand the Extract transformation as a subset of the SQL join transformation. It happened to me more than once that I needed to modify an extract step by adding a table for further selection. When using an Extract transformation this means to redo everything (replace it with a SQL join), when using a SQL join transformation from the beginning then it's just modifying what's already there.
(in the SQL join transformation diagram view simply delete the "join" node and you get what the Extract transformation does).
02-28-2012 09:22 PM
Good point Patrick. This might work instead, if SAS SQL can optimize out the constant before sending it to Oracle:
where datestamp >= intnx("MINUTE", DATETIME(), -30, "SAME")
02-28-2012 10:01 PM
I can't see intnx() listed as a function passed to Oracle
I understand that you have in mind that eventually SAS converts the intnx() function to a constant and then passes this constant to Oracle for execution (converting it to a value appropriate for Oracle datetimes).
I don't think that this can/should work this way but that it's always about converting SAS functions to Oracle functions and then send the command to Oracle for execution.
I've seen more than one environment where clocks on different servers were not in sync. So using intnx() with datetime() on the SAS side to generate a value would use the SAS server clock but then execute the where clause on the Oracle server with the clock there.
02-28-2012 10:04 PM
The result of intnx which in this case will be a datetime constant would be passed to Oracle. That is just a guess, of course. One must try it.
02-29-2012 01:40 AM
I don't think so. I often use the technique to create date or datetime constants stored in macro variables.
This can be done in the pre code of the job or in a transformation.
The Oracle libname engine should be able to convert them properly.
02-29-2012 05:14 AM
You're then passing this like "&DateString"d right? That should work and be sent to Oracle using function to_date(...).
The issue with clocks not in sync on different servers remains. I've seen this in real life situations.
03-01-2012 06:13 AM
Since they extract half an hour at the time, it doesn't seem like a real time application.
So I would postpone the extract with a reasonable margin (according to differences between computer clocks), and the use BETWEEN-AND logic in the extract.
If the source data base is in a different time zone, the that has to be taken care of in the pre code.
03-01-2012 03:29 PM
Given that "sysdate" in Oracle is a datetimestamp, one could use sysdate- (1800) (with the appropriate syntax around it) as part of the Oracle passthru where clause ...