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