Pass-Through Facility: PC Files on Linux, UNIX, and 64-Bit Microsoft Windows

CONNECTION TO Component

Retrieves and uses data source data in a PROC SQL query or view.

Valid in:

PROC SQL step SELECT statements

Syntax
Arguments
Details
Examples

Syntax

CONNECTION TO PCFILES <AS alias> <(database-connection-options)>

Arguments

alias: specifies an alias if one was defined in the CONNECT statement.
database-connection-arguments: specifies the data source-specific arguments that are needed by PROC SQL to connect to the data source. These arguments are not required and the default behavior opens a dialog box.

Details

The CONNECTION TO component specifies the data source connection that you want to use or that you want to create (if you have omitted the CONNECT statement). CONNECTION TO then enables you to retrieve data source data directly through a PROC SQL query.

You use the CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement:

SELECT column-list

FROM CONNECTION TO data source-name (data source-query);

CONNECTION TO can be used in any FROM clause, including those in nested queries (that is, in subqueries).

You can store a Pass-Through Facility query in a PROC SQL view and then use that view in SAS programs. When you create a PROC SQL view, any options that you specify in the corresponding CONNECT statement are also stored. So when the PROC SQL view is used in a SAS program, SAS can establish the appropriate connection to the data source.

Because external data sources and SAS have different naming conventions, some data source column names might be changed when you retrieve data source data through the CONNECTION TO component.

Examples

Query Tables or Subtables

After the connection, this example uses the CONNECTION TO component to query a table or a subtable:

SELECT * FROM CONNECTION TO PCFILES (SELECT * FROM `my invoice`);
SELECT * FROM CONNECTION TO PCFILES
(SELECT`Invoice Number`, Amount from `my invoice`);

Create a SAS Data Set from a Microsoft Access Table

This example creates a SAS data set called Newtable from a Microsoft Access table:

PROC SQL;
CONNECT TO PCFILES (SERVER=d2323 PATH=' c:\temp\household.inventory.mdb' );
CREATE TABLE newtable AS SELECT*FROM;
CONNECT TO PCFILES(SELECT*FROM rooms);
DISCONNECT FROM PCFILES;
QUIT;

Query a Table Range in an Excel Workbook

This example connects to an Excel file and queries the INVOICE table (range) within the Excel workbook:

PROC SQL DQUOTE=ANSI;
CONNECT TO PCFILES (PATH=' c:\sasdemo\sasdemo.xls' server=d2323 );
SELECT * FROM CONNECTION TO PCFILES
  (SELECT * FROM invoice);
DISCONNECT FROM PCFILES;
QUIT;

Use PC Files Server to Create a SAS Data Set

This example uses a PC Files Server through ODBC to create a SAS data set called Work from a list of tables in a Microsoft SQL Server database:

PROC SQL DQUOTE=ANSI;
CONNECT TO PCFILES (SERVER=d2323 DSN=mqis USER=scott PWD=tiger);
CREATE TABLE work AS SELECT * FROM CONNECTION TO PCFILES (PCFILES::SQLTABLES);
DISCONNECT FROM PCFILES;
QUIT;

Top of Page