topic How to extract data for last 1/2 hour? in SAS Data Management

How to extract data for last 1/2 hour?

Halaku — Tue, 28 Feb 2012 20:38:50 GMT

Hi,

I am looking for a SAS equivalent for the following:

TIMESTAMP >=SYSDATE-30/1440

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.

Thanks,

How to extract data for last one hour?

PGStats — Tue, 28 Feb 2012 20:55:33 GMT

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.

How to extract data for last 1/2 hour?

Halaku — Tue, 28 Feb 2012 21:12:07 GMT

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.

How to extract data for last 1/2 hour?

PGStats — Tue, 28 Feb 2012 21:22:05 GMT

I am not familiar with Oracle, bu I assume that the proper translation is done with timestamps when imported to SAS. Good luck. - PG

Re: How to extract data for last 1/2 hour?

Patrick — Wed, 29 Feb 2012 01:59:31 GMT

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).

Re: How to extract data for last 1/2 hour?

PGStats — Wed, 29 Feb 2012 02:22:31 GMT

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")

- PG

Re: How to extract data for last 1/2 hour?

Patrick — Wed, 29 Feb 2012 03:01:10 GMT

I can't see intnx() listed as a function passed to Oracle

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113612.htm

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.

Re: How to extract data for last 1/2 hour?

PGStats — Wed, 29 Feb 2012 03:04:37 GMT

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.

Re: How to extract data for last 1/2 hour?

Patrick — Wed, 29 Feb 2012 03:22:50 GMT

See my previous post. Sorry - was updating it while you posted your reply.

Re: How to extract data for last 1/2 hour?

PGStats — Wed, 29 Feb 2012 03:34:59 GMT

You are right Patrick. Then Halaku is stuck with using a pass-through query :smileycry: !

Re: How to extract data for last 1/2 hour?

LinusH — Wed, 29 Feb 2012 06:40:35 GMT

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.

Re: How to extract data for last 1/2 hour?

Patrick — Wed, 29 Feb 2012 10:14:59 GMT

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.

How to extract data for last 1/2 hour?

LinusH — Thu, 01 Mar 2012 11:13:15 GMT

That's right.

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.

How to extract data for last 1/2 hour?

Bill — Thu, 01 Mar 2012 20:29:51 GMT

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 ...

Re: How to extract data for last 1/2 hour?

Halaku — Mon, 05 Mar 2012 14:45:52 GMT

Over the weekend I tried a few things, not pretty and I do not understand it fully but it worked.

Here is what I used to perform the extraction:

UPDATETIME > %sysfunc(datetime())- (60*30)

I still have to understand fully the working of %sysfunc, can any one explain? Is this the right approach?:smileyconfused:

I am using DIS 4.21

How to extract data for last 1/2 hour?

Halaku — Mon, 05 Mar 2012 18:47:06 GMT

I have put a reply, please read and if you have any suggestions...