Connecting to Common Data Sources

Establishing Connectivity to an Excel File

Overview of Establishing Connectivity to an Excel File

The following figure provides a logical view of using an Excel file as a data source.

[Establishing Connectivity to an Excel File]

The Excel file must be stored in a location that can be accessed by a Windows 32-bit machine. This example focuses on a file that is local to a SAS server, but a Windows UNC path such as \\datasrv\sales\firstquarter.xls is also acceptable.

To establish connectivity to an Excel file, perform the following steps:

In SAS Management Console, expand Data Library Manager. Right-click Libraries. Then, select the New Library option to access the New Library wizard.
Select Microsoft Excel Library from the Database Data list. Click Next.
Enter a value for Libref and click Next.
Enter an appropriate library name in the Name field (for example, FirstQuarterSales). Specify a metadata folder location for the library in the Library field. You can supply an optional description. Click Next.
Select one or more SAS servers and click the right arrow. The library is available to the servers included in this list and visible to users of the server. Click Next.
On the server and connection page, click New in the Server Details group box to specify a new database server.

The New Server wizard displays.
Enter a name such as FirstQuarterSalesFolder in the Name field. Click Next.
Click Next on the Server Properties page.
On the Connection Properties page, enter the path to the Excel file. Enclose the value in quotation marks. For example, "C:\sales\firstquarter.xls." Click Next.
Examine the final page of the New Server wizard to ensure that the proper values have been entered. Click Finish to save the server settings.

The New Server wizard closes and returns you to the final page of the New Library wizard.
Examine the final page of the New Library wizard to ensure that the proper values have been entered. Click Finish to save the library settings.

At this point, you can register tables, as explained in Registering and Verifying Tables.

The Microsoft Excel Library wizard in SAS Management Console generates the metadata to construct a LIBNAME statement for the EXCEL LIBNAME engine. For more information about the EXCEL LIBNAME engine and supported options, see the SAS/ACCESS Interface to PC Files: Reference.

Top of Page