SAS/ACCESS Software for Relational Databases: Reference

Examples

The following example sends an Oracle SQL query, presented in italic type, to the Oracle database for processing. The results from the Oracle SQL 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
   (dsn=ora7 uid=testuser pwd=testpass);

select *
   from connection to mycon
      (select empid, lastname, firstname,
      hiredate, salary
          from sasdemo.employees
          where hiredate>='31.12.1988');

disconnect from mycon;
quit;

The following example gives the previous query a name and stores it as the PROC SQL view SLIB.HIRES88. The CREATE VIEW statement appears in italics.

libname slib 'SAS-data-library';

proc sql;
connect to odbc as mycon
   (dsn=ora7 uid=testuser pwd=testpass);

create view slib.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;

The next example connects to Microsoft Access 7 and creates a view NEWORDERS from all the columns in the ORDERS table.

proc sql;
   connect to odbc as mydb
      (dsn=access7);
   create view neworders as
    select * from connection to mydb
      (select * from orders);
disconnect from mydb;
quit;

This ODBC example sends an SQL query to Microsoft SQL Server 6.5 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. In this example, MYDB is the connection alias.

proc sql;
   connect to odbc as mydb
      (dsn="SQL Server" uid=testuser pwd=testpass);
   select * from connection to mydb
      (select CUSTOMER, NAME, COUNTRY
          from CUSTOMERS
          where COUNTRY <> 'USA');
quit;

The next ODBC example returns a list of the columns in the CUSTOMERS table.

proc sql;
   connect to odbc as mydb
      (dsn = "SQL Server" uid=testuser pwd=testpass);
   select * from connection to mydb
      (ODBC::SQLColumns (, , "CUSTOMERS"));
quit;

Date and Time Formats

Date, time, and datetime formats require the following syntax when used in ODBC.

For date:: {d 'yyyy-mm-dd'}
For time:: {t 'hh:mm:ss'}
For timestamp:: {ts 'yyyy-mm-dd hh:mm:ss[.ffff]'}

This example uses the date format. It inserts three columns into a table named NEW_HIRES that is in a Microsoft Access 7 database named NORTHWIND. An SQL pass-through view is created for all new hires where CITY=SEATTLE.

proc sql;
connect to odbc (dsn=northwind);
execute (create table new_hires
   (lastname char(15), firstname char(10), hired date, 
      city char(15))) by odbc;

execute (insert into new_hires
    values('Jones','Fred',{d '1998-01-11'}, 'Seattle')) 
    by odbc;
execute (insert into new_hires
    values('Gomez','Maria',{d '1997-12-05'}, 'Tacoma')) 
    by odbc;
execute (insert into new_hires
    values('Smith','Mary',{d '1998-02-16'}, 'Seattle')) 
    by odbc;

create view region as
   select * from connection to odbc
     (select * from new_hires where city='Seattle');

disconnect from odbc;
quit;

proc print data=region;
title 'New Employees in Seattle Office';
run;

Chapter Contents
Previous
Next
Top of Page