We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Run RDBMS-SQL

by niconi on ‎04-04-2014 02:19 AM - edited on ‎10-05-2015 06:19 PM by Community Manager (371 Views)

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.

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.