This example sends an
Oracle SQL query (presented in highlighted text) to the Oracle database
for processing. The results from the query serve as a virtual table
for the PROC SQL FROM clause. In this example MYCON is a connection
alias.
proc sql;
connect to odbc as mycon
(datasrc=ora7 user=testuser password=testpass);
select *
from connection to mycon
(select empid, lastname, firstname,
hiredate, salary
from sasdemo.employees
where hiredate>='31.12.1988');
disconnect from mycon;
quit;This next example gives
the previous query a name and stores it as the SQL view Samples.Hires88.
The CREATE VIEW statement appears highlighted.
libname samples 'SAS-library';
proc sql;
connect to odbc as mycon
(datasrc=ora7 user=testuser password=testpass);
create view samples.hires88 as
select *
from connection to mycon
(select empid, lastname, firstname,
hiredate, salary from sasdemo.employees
where hiredate>='31.12.1988');
disconnect from mycon;
quit;This example connects
to Microsoft Access and creates a view NEWORDERS from all columns
in the ORDERS table.
proc sql;
connect to odbc as mydb
(datasrc=MSAccess7);
create view neworders as
select * from connection to mydb
(select * from orders);
disconnect from mydb;
quit;This next example sends
an SQL query to Microsoft SQL Server, configured under the data source
name
SQL Server, for processing. The
results from the query serve as a virtual table for the PROC SQL FROM
clause.
proc sql;
connect to odbc as mydb
(datasrc="SQL Server" user=testuser password=testpass);
select * from connection to mydb
(select CUSTOMER, NAME, COUNTRY
from CUSTOMERS
where COUNTRY <> 'USA');
quit;This example returns
a list of the columns in the CUSTOMERS table.
proc sql;
connect to odbc as mydb
(datasrc="SQL Server" user=testuser password=testpass);
select * from connection to mydb
(ODBC::SQLColumns (, , "CUSTOMERS"));
quit;