DATA Step, Macro, Functions and more

Implicit vs Explicit SQL Pass through SQL Query in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Implicit vs Explicit SQL Pass through SQL Query in SAS

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

 

 


Accepted Solutions
Solution
‎04-07-2016 06:12 PM
PROC Star
Posts: 1,561

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

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;

View solution in original post


All Replies
Super User
Posts: 17,831

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

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. 

Super User
Posts: 5,257

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

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

Data never sleeps
Solution
‎04-07-2016 06:12 PM
PROC Star
Posts: 1,561

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

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;
New User
Posts: 1

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

[ Edited ]

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 3750 views
  • 5 likes
  • 5 in conversation