Hybrid Queries (from Fraktal SAS Programming)

What is this?

The qualifying term "hybrid" denotes to the fact that a single piece of SQL code is processed by a number of systems that are integrated by appropriate middleware in a way that system borders are not visible when looking at the code.

We will use a maximum simplified task to be coded in SQL:

• A SAS dataset shall be created
• Data for which are kept in a DBMS table
• The DBMS talks ANSI SQL
• SAS can connect to the DBMS using SAS/Connect software
• The solution uses Proc SQL from Base SAS software

Documented Code Example

Code executed	Function performed
proc sql;	Start the SQL interpreter inside SAS
connect to oracle (user="&DB_USR." password="&DB_PWD." path="&DB_PTH." );	Open connection to the DBMS using your credentials stored in Macro Variables
create table my_sas_tab as	Initiate creating your SAS table using ANSI SQL code
select * from connection to oracle	Start your query from inside SAS with a special from clause
( select * from ops$&DB_USR..&MYTABLE. )	Inside brackets use DBMS specific SQL; Macro Variables are resolved before code is passed to any processing system
;	Execute the query by issuing the SAS statement terminator ';'
disconnect from oracle;	Close connection to DBMS
quit;	Terminate SAS SQL interpreter status