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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

4 REPLIES 4
Reeza
Super User

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. 

LinusH
Tourmaline | Level 20

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
ChrisNZ
Tourmaline | Level 20

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;
srinath
Calcite | Level 5

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 37247 views
  • 7 likes
  • 5 in conversation