BookmarkSubscribeRSS Feed

Run RDBMS-SQL

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

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 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags