BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bheinsius
Lapis Lazuli | Level 10

Hi,

I need to specify NOIPASSTHRU on the PROC SQL code that the DIS 4.901 Extract transformation generates to prevent query from passing thru to SPDS.
Any way to do that?

Bart

1 ACCEPTED SOLUTION

Accepted Solutions
bheinsius
Lapis Lazuli | Level 10

I changed from the SQL Extract to the SQL Join transformation and not joining anything, which lets me specify proc sql options.

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20
Why would you like to that? Can't really imagine the situation when an extract is better performed by the SAS client.
The only way I know of is to avoid use the IP=YES LIBNAME option.
But that can be quite awkward in a metadata environment if you wish to use IP=YES in other situations.
Data never sleeps
bheinsius
Lapis Lazuli | Level 10

SPDS still uses SQL optimizer from SAS 6.12

Some queries run much faster when processed by SAS 9.4. Mine goes from 2 minutes to 18 seconds when not passing through.

David_Luttrell
Obsidian | Level 7

By default all commands are carried out in the SAS engine so you should be okay. As part of a PROC SQL statement you need to stipulate that the SQL is being passed through to the database server. e.g. below:

 

proc sql outobs=15;
   connect to oracle as ora2 (user=user-id password=password);
   select * from connection to ora2 (select lname, fname, state from staff);
   disconnect from ora2;
quit;

 

You could try and lock this down even further by tightening how your Library connects to the DB

 

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342247.htm

bheinsius
Lapis Lazuli | Level 10

SAS passes the query down to SPDS automatically unless I specify NOIPASSTHRU (implicit pass-thru this is called).

My problem is that i can't find an option in the extract transformation that prevents the passthru.

There is a NODBIDIRECTEXECT option but this doesn't work for SPDS (i tried).

 

bheinsius
Lapis Lazuli | Level 10

I changed from the SQL Extract to the SQL Join transformation and not joining anything, which lets me specify proc sql options.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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
  • 5 replies
  • 1636 views
  • 0 likes
  • 3 in conversation