BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Halaku
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Halaku
Obsidian | Level 7

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

View solution in original post

15 REPLIES 15
PGStats
Opal | Level 21

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
Halaku
Obsidian | Level 7

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.

PGStats
Opal | Level 21

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
Patrick
Opal | Level 21

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

PGStats
Opal | Level 21

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
Patrick
Opal | Level 21

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.

PGStats
Opal | Level 21

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
Patrick
Opal | Level 21

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

PGStats
Opal | Level 21

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

PG

PG
LinusH
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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.

LinusH
Tourmaline | Level 20

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
Bill
Quartz | Level 8

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

Halaku
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 15 replies
  • 14245 views
  • 1 like
  • 5 in conversation