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
I changed from the SQL Extract to the SQL Join transformation and not joining anything, which lets me specify proc sql options.
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.
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
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).
I changed from the SQL Extract to the SQL Join transformation and not joining anything, which lets me specify proc sql options.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.