SASBI.net

ODBC (Open DataBase Connectivity) is a technology to connect something (called client) to a database. It is widely used to download data to SAS from other DBs. But one can also use

ODBC to download data from SAS. For instance one can connect Excel to SAS to copy data from SAS into Excel skipping temporary CSV files or to use Excel as a DWH client.

There is an instruction to configure SAS ODBC driver available on SAS.com. We will follow the instruction and connect Excel to SAS.

Driver installation

First of all one has to download ODBC driver. The driver is free but registration on SAS.com is required. To download the driver go to SAS.com → Support & Training → Support → Downloads & Hot Fixes → SAS Software → ODBC Drivers. After downloading the sasodbc.exe click it and install the driver. The installation is straightforward.

Driver configuration

After installing the driver open the file C:WINDOWSsystem32driversetcservises. The file can be in a different folder depending on the OS. Add the following string to the end of the file:

shr1        1234/tcp      # My SAS Server

The string defines the service name that will listen to the connection to ODBC driver from the tcp protocol, port 1234. The port number should be unique.

To configure the driver:

1. Open the ODBC data source administrator window: Start → Control Panel → Administrative tools → Data Sources (ODBC).
2. On the User DSN tab click Add.
3. Choose “SAS” from the list and click Finish.
4. Data Source Name field: type “SAS ODBC”.
5. Servers tab: in the Name field type service name: shr1.
6. Click Configure, OK and Add.
7. Libraries tab: type any library name in the field Name. The library will be available for clients accessing SAS through ODBC driver. The libraries defined in the autoexec.sas file will be unavailable.
8. Host File: type the library catalog.
9. Click ADD, OK, OK.

Connect Excel to SAS

The instruction bellow is for Excel 2007 and differs a little for the Excel 2003.

1. Run Excel.
2. Go to Data tab → From other Sources → From Microsoft Query.
3. Chose “SAS ODBC*” from the list and click OK.
4. Window with a list of SAS tables will appear after SAS Start.

5. Move one of the tables from the left list to the right.
6. Click Next, Next, Next, Finish, Ok.
7. The data will appear in Excel.

Microsoft Query Wizard also allows building SQL queries and sending it to SAS for execution. One has to choose two tables and after clicking Next the Microsoft Query window will apper.

Add-in for MS Office

There is a specialized SAS product that allows working with SAS through Excel. It has an advantage over ODBC driver. Add-in for MS Office allows using SAS wizards to run math algorithms in SAS that are not yet available in Excel. But if you don’t use math ODBC driver is the best choice as it is free.