Challenge
A while ago a customer asked me for help on running native Oracle SQL on a database through Enterprise Guide. He said he needed a username/password to achieve this. My first thought was that this should be possible using a pre-assigned library (so we didn't have to give out passwords which could be used with other tools than SAS). I looked at it with SAS-Support and we couldn't find a way to achieve this through metadata.
Then we found a mention of 'CONNECT USING {libref}' in the SAS 9.4 documentation next to the option 'CONNECT TO dbms-name... ' Well, that looked like a solution for our challenge but we are running SAS 9.3. SAS support then found out the option was supported in SAS 9.3. Unfortunately the option is not documented in the SAS-documentation where it should and it is poorly documented in SAS 9.4 in my opinion.
Solution
When you want to run sql like 'select sysdate from dual;' in Enterprise Guide you can use this syntax:
PROC SQL;
CONNECT USING {LIBREF};
SELECT * FROM CONNECTION TO {LIBREF} (select sysdate from dual) ;
QUIT;
Conclusion
CONNECT USING allows my customers to use all the possibilities of Oracle within SAS Enterprise Guide offering a choice between SAS SQL or RDBMS native SQL. It is a great option, SAS should advertise this option.
Thanks to Jan Lauffer of SAS-support who helped me to find this solution.
