Hybrid Queries (from Fraktal SAS Programming): Unterschied zwischen den Versionen

Aus phenixxenia.org
Zur Navigation springen Zur Suche springen
K
K
 
(3 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 8: Zeile 8:
 
|übersicht=DBMS Interaction (from Fraktal SAS Programming)
 
|übersicht=DBMS Interaction (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 ==
 
== Documented Code Example ==
Zeile 42: Zeile 54:
 
   from ops$&DB_USR..&MYTABLE.
 
   from ops$&DB_USR..&MYTABLE.
 
  )
 
  )
| '''Inside brackets use DBMS specific SQL'''
+
| '''Inside brackets use DBMS specific SQL; [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]] are resolved before code is passed to any processing system'''
 
|-
 
|-
 
|
 
|

Aktuelle Version vom 2. Juli 2014, 14:32 Uhr

Zurück

Übersicht

Vorwärts

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

Zurück

Übersicht

Vorwärts