BookmarkSubscribeRSS Feed

Run RDBMS-SQL

Started ‎04-04-2014 by
Modified ‎10-05-2015 by
Views 1,230

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.

Version history
Last update:
‎10-05-2015 06:19 PM
Updated by:
Contributors

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

SAS AI and Machine Learning Courses

The rapid growth of AI technologies is driving an AI skills gap and demand for AI talent. Ready to grow your AI literacy? SAS offers free ways to get started for beginners, business leaders, and analytics professionals of all skill levels. Your future self will thank you.

Get started

Article Labels
Article Tags