DATA Step, Macro, Functions and more

How can I write a basic SQL passthrough query?

Reply
Contributor
Posts: 29

How can I write a basic SQL passthrough query?

Hi, I'm trying to write a basic passthrough query using proc sql. The query as it stands at the moment is;


LIBNAME sdat oracle user=abcd password=1234 path='abc' schema='def' ;

PROC SQL ;
CREATE TABLE outdat AS
SELECT *
FROM sdat.act
WHERE ddate GE '01Aug2010'd
;
QUIT ;


This works as you would expect, returinng the right number of observations from the table where the date is greater or equal to 1st Aug 2010.

I am trying re-write this in the following manner as I have been told it is more efficient and will run quicker;


PROC SQL ;
CONNECT TO ??? AS myconn ;
CREATE TABLE outdat AS
SELECT *
FROM connection to myconn (
SELECT *
FROM sdat.act
WHERE ddate GE '01Aug2010'd
)
;
QUIT ;


There are a few of questions I have about this;

1) Is this really more efficent and will it run quicker?

2) How do I code the 'connect to' part of the passthrough sql so that it correctly points at the oracle server? I figure that I must have all of the information to do this as it's in the LIBNAME statement in the first piece of the code but can't figure out a way of doing this.


Thanks in advance for any help.

Fat Captain.
Respected Advisor
Posts: 4,173

Re: How can I write a basic SQL passthrough query?

Posted in reply to FatCaptain
Hi

You have been told wrong.

What SAS actually does is translating the SAS SQL into Oracle SQL and then send this Oracle SQL flavour to the DB.

Using the following option in your code will show you in the Log what SQL is sent to the DB:
options sastrace=',,,ds' sastraceloc=saslog nostsuffix;


There are some functions which SAS can't translate into Oracle syntax. What SAS then does is to pass a SQL without these functions to Oracle and then use the non translatable functions on the result set returned from Oracle.

This might cause performance issues as may be exactly this function would reduce the result set dramatically.

Using the option above shows you what SAS actually could pass to Oracle for execution.
Sometimes a small change to your SAS SQL code can result in SAS sending the full query to Oracle

Below the list of SAS functions which can be translated (and it's getting more and more with every release):
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl...

The only cases where writing a pass through SQL makes sense in my opinion is:
- You want to use Oracle specific functionality or functions (i.e. row() ).
- SAS is not able to send part of your query to Oracle which results in a much bigger result set sent back from the DB and you can't re-formulate the SAS SQL in a way that the full code is sent to the DB.

And here some SAS Online doc examples for SQL pass-through to ORACLE:
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl...


HTH
Patrick

Message was edited by: Patrick
Contributor
Posts: 29

Re: How can I write a basic SQL passthrough query?

Thanks for this Patrick.

I was told the passthrough query would run the sql on the oracle server and only return the results but running it as I was doing would return the entire dataset for my local pc to do the processing. Thanks for clarifying.

Following on from the second point you raised for wanting to use sql passthrough queries, I'm having difficulty specifying a date criteria in my sql code. The format on the oracle database is datetime, e.g. 01Sep2010:01:25:49
I'm using the datepart function to seperate the date out (the link to the documentation you provided confirms that SAS is capable of translating this) but it's having no effect. All records are being returned regardless of the date value. Are there any quirks that I should be aware of between date formats on SAS / oracle?

Cheers,

Fat Captain.
Respected Advisor
Posts: 4,173

Re: How can I write a basic SQL passthrough query?

Posted in reply to FatCaptain
Hi

If you use...
options sastrace=',,,ds' sastraceloc=saslog nostsuffix;

... you will see in your log what code actually is sent to the DB for execution - and may be a bit of tweaking your where clause and everything will be sent.

The link for functions tranlated into DB syntax is for SAS9.2. If you're using a older SAS version then lookup the list of functions in the doc for this version. Datepart() for example didn't get translated in SAS9.1.3.

How does your where clause look like?

where datepart(ddate) GE '01Aug2010'd

It could be that your SAS version doesn't translate '01Aug2010'd into a Oracle datetime value (again, the SAS log will tell you).

You could try:
where datepart(ddate) GE input('01Aug2010',date9.)

or may be:
where ddate GE input('01Aug2010:00:00:00', datetime21.)

If you really want to write this as explicit pass-through SQL then you have to do it in Oracle SQL syntax. Lookup (in a Oracle manual) char_to_date and similar functions.

HTH
Patrick

Message was edited by: Patrick Message was edited by: Patrick
Super Contributor
Posts: 292

Re: How can I write a basic SQL passthrough query?

I typically use code as simple as this

select * from connection to oracle
(
select columns

from file

where trunc(date) > '25-nov-09'

);
Ask a Question
Discussion stats
  • 4 replies
  • 669 views
  • 0 likes
  • 3 in conversation