topic Implicit vs Explicit SQL Pass through SQL Query in SAS in SAS Programming

Implicit vs Explicit SQL Pass through SQL Query in SAS

dkanand86 — Wed, 06 Apr 2016 19:16:53 GMT

Hi Everyone,

Please can you let me know the Difference between Implicit vs Explicit Pass through Query.

Support your answer with an example.

Thanks!

Regards,

Deepanker

Re: Implicit vs Explicit SQL Pass through SQL Query in SAS

Reeza — Wed, 06 Apr 2016 19:26:37 GMT

Explicit passes the query to the DB to be executed. The commands are literally passed directly to the server, and as such need to be db compliant, ie if it's an oracle database, its Oracle SQL. All data must exist on the server. You can also use explicit pass through to execute a stored process on the server.

Implicit uses SAS SQL. Depending on the db certain functions may or may not be passed through, the documentation lists which are passed through for each database. Depending on the query, processing may occur on db or on SAS side. If you're using data from work library and server, then the processing happens on the SAS side and the data is pulled down from the SQL server.

I'm not sure how an example would help here, because it's such a general question and dependent on many things, the database type, the functions in the query and where the data exists, ie on server or on desktop.

I'm also 99% sure there are better answers than mine on here so searching may get you a better response.

General question = general response.

Re: Implicit vs Explicit SQL Pass through SQL Query in SAS

LinusH — Wed, 06 Apr 2016 21:00:15 GMT

Just want to add some personal thoughts.

I always favour implicit pass thru. This because your code will be DB independent (sort of), and easier to write and read (less statements and nesting).

I only explore explicit pass thru if I hit performance bottlenecks (such as described by @Reeza), or if there are any very specific native functionality I need to use (SQL dialect, or Stored Procedure to mention a few).

Re: Implicit vs Explicit SQL Pass through SQL Query in SAS

ChrisNZ — Wed, 06 Apr 2016 22:08:14 GMT

An explicit SQL Pass through is coded as such by you.

Your proc sql contains a connection to the database and a request to use that connection, such as:

proc sql;
connect to oracle (user=myusr1 password=mypwd1);
select *
   from connection to oracle
     (select * 
      from customers
      where customer like '1%');
    disconnect from oracle;
quit;

An implicit pass through uses standard sas code and lets sas do the connection and translation work for you, behind the scene.

proc sql;
     select * 
     from ORALIB.CUSTOMERS      
     where CUSTOMER like '1%';
quit;

Re: Implicit vs Explicit SQL Pass through SQL Query in SAS

srinath — Thu, 27 Apr 2017 05:18:36 GMT

explicit pass through query is nothing but when we connect to the data base the query which hits the database and make the required modifications in the db and imports the data. Here the total execution takes place in db.
when we code explicit pass through query the sas functions wont work. for example we are connecting to oracle we have to write oracle functions only.

Proc sql;
connect to oracle (user=XXX password=XXX);
select * from connection to oracle
(select * from table);
disconnect from oracle;
quit;

in implicit pass through query the execution takes place in sas server itself and imports the data.

proc sql;
select * from table
where condition;
quit;

or
Libname libref "path";