SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to extract data for last 1/2 hour?

Reply
Contributor
Posts: 38

How to extract data for last 1/2 hour?

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,

Respected Advisor
Posts: 4,920

How to extract data for last one hour?

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.

PG

PG
Contributor
Posts: 38

How to extract data for last 1/2 hour?

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.

Respected Advisor
Posts: 4,920

How to extract data for last 1/2 hour?

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

PG
Respected Advisor
Posts: 4,173

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

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

Respected Advisor
Posts: 4,920

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

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

PG
Respected Advisor
Posts: 4,173

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

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.

Respected Advisor
Posts: 4,920

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

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.

PG

PG
Respected Advisor
Posts: 4,173

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

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

Respected Advisor
Posts: 4,920

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

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

PG

PG
Super User
Posts: 5,426

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

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.

Data never sleeps
Respected Advisor
Posts: 4,173

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

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.

Super User
Posts: 5,426

How to extract data for last 1/2 hour?

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.

Data never sleeps
Super Contributor
Posts: 291

How to extract data for last 1/2 hour?

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

Contributor
Posts: 38

How to extract data for last 1/2 hour?

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

Ask a Question
Discussion stats
  • 15 replies
  • 3702 views
  • 1 like
  • 5 in conversation