Sample of LIBNAME and PROC SQL Pass-Through code to connect to other database using SAS/ACCESS Interface to ODBC. You can access data from ODBC compliant databases using SAS/ACCESS Interface to ODBC with an ODBC driver and necessary client software. You can access the database using either the ODBC LIBNAME engine or the Pass-Through Facility with the SQL procedure.

A LIBNAME engine provides a seamless and transparent way to surface data. It requires minimal knowledge of the database and the Structured Query Language (SQL). Once you define the LIBNAME, you can use database tables the same as a SAS data set.

An example below, before using the dsn= (Data Source Name) from SAS, you need make sure that you configure this data source using the ODBC Administrator on your PC. You will also need to substitute your own database connection information for userid and password information.

LIBNAME statement without using Data Source Name, to Microsoft SQL Server.

LIBNAME statement using NT Authentication to Microsoft SQL Server.

Once you assign the LIBNAME, you can view your database tables in your library in the SAS Explorer window or, if you prefer, you can submit a DATASETS procedure that is similar to the following to display the tables in the SAS log.

In the following example, the KEEP data set option causes the SAS/ACCESS engine to select only the SALARY and DEPT columns when it reads the EMPLOYEES table:

The PROC SQL Pass-Through Facility requires that you have knowledge of the SQL that is passed to the database via PROC SQL.

In the above example, the second SELECT statement (in the parentheses) is passed to the database via the ODBC driver 'as is'. Temporary SAS data set ‘job204’ is created from the database table employees containing jobcode 204.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS/ACCESS Interface to ODBC	Microsoft® Windows® for 64-Bit Itanium-based Systems	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows XP 64-bit Edition	9.21_M2		9.2 TS2M3
		Microsoft® Windows® for x64	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Datacenter Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Enterprise Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Standard Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 for x64	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2008	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2008 for x64	9.21_M2		9.2 TS2M3
		Microsoft Windows XP Professional	9.21_M2		9.2 TS2M3
		Windows 7 Enterprise 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Enterprise x64	9.21_M2		9.2 TS2M3
		Windows 7 Home Premium 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Home Premium x64	9.21_M2		9.2 TS2M3
		Windows 7 Professional 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Professional x64	9.21_M2		9.2 TS2M3
		Windows 7 Ultimate 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Ultimate x64	9.21_M2		9.2 TS2M3
		Windows Vista	9.21_M2		9.2 TS2M3
		Windows Vista for x64	9.21_M2		9.2 TS2M3
		64-bit Enabled AIX	9.21_M2		9.2 TS2M3
		64-bit Enabled HP-UX	9.21_M2		9.2 TS2M3
		64-bit Enabled Solaris	9.21_M2		9.2 TS2M3
		HP-UX IPF	9.21_M2		9.2 TS2M3
		Linux	9.21_M2		9.2 TS2M3
		Linux for x64	9.21_M2		9.2 TS2M3
		Solaris for x64	9.21_M2		9.2 TS2M3

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This is a sample of LIBNAME and PROC SQL Pass-Through code to connect to ODBC compliant database using SAS/ACCESS Interface to ODBC. The code cannot run sucessfully stand-alone since you need to provide your ODBC connection information and database table name.

---

libname odbclib odbc uid=id pwd=password dsn=test;

libname odbclib odbc noprompt='server=server.sas.com;DRIVER=SQL Server;userid=id;password=password;database=test';


libname odbclib odbc noprompt='server=server.sas.com;DRIVER=SQL Server;Trusted Connection=yes;database=test';


proc datasets lib=odbclib;
quit;

proc sql;
connect to odbc(dsn=test uid=id password=password);
    create table work.job204 as
   select * from connection to odbc
   (select * from employees where jobcode=204);
quit;

NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.2 (TS2M3)
      NOTE: This session is executing on the X64_VSPRO  platform.



NOTE: SAS initialization used:
      real time           0.68 seconds
      cpu time            0.54 seconds

1    libname odbclib odbc uid=dbitest pwd=XXXXXXX dsn=greenplum;
NOTE: Libref ODBCLIB was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: greenplum
2
3
4
5    libname odbclib odbc
5  ! noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
5  ! XXX;
NOTE: Libref ODBCLIB was successfully assigned as follows:
      Engine:        ODBC
      Physical Name:
6
7
8
9
10   libname odbclib odbc
10 ! noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;

NOTE: Libref ODBCLIB was successfully assigned as follows:
      Engine:        ODBC
      Physical Name:
11
12
13   proc datasets lib=odbclib;
                                              Directory

                                        Libref         ODBCLIB
                                        Engine         ODBC
                                        Physical Name
                                        Schema/Owner   varsha


                                                           DBMS
                                                   Member  Member
                                     #  Name       Type    Type

                                     1  cars       DATA    TABLE
                                     2  charfirst  DATA    TABLE
                                     3  class      DATA    TABLE
                                     4  foo1       DATA    TABLE
                                     5  numfirst   DATA    TABLE
                                     6  vxdhelp    DATA    TABLE
14   quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.07 seconds
      cpu time            0.04 seconds


15
16   proc sql;
17   connect to odbc(dsn=sqlserver uid=dbitest password=XXXXXXX);
18       create table work.job204 as
19      select * from connection to odbc
20      (select * from employees where jobcode=204);
NOTE: Table WORK.JOB204 created, with 7 rows and 11 columns.

21   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

Sample of LIBNAME and PROC SQL Pass-Through code to connect to other database using SAS/Access® Interface to ODBC

Type:	Usage Note
Priority:
Topic:	Data Management ==> Data Sources ==> External Databases ==> ODBC (Open Database Connectivity)

Date Modified:	2010-12-24 12:34:50
Date Created:	2010-12-16 16:13:33