Using SAS ACCESS and PROC SQL to Retrieve SQL Server Data

By: Rick Dobson | Comments (3) | Related: 1 | 2 | 3 | 4 | > Other Database Platforms

Problem

Have you been tasked with transferring SQL Server data for several different SAS analysis packages or even for a single package on a recurring basis? Are the precise inputs for the SAS pack(s) subject to change? Do you have to enable SAS developers to create their own solutions with current SQL Server data?

Solution

Two previous articles Transferring data from SAS to SQL Server and back and Using Wizards to Export a Dataset from SAS to SQL Server describe and demonstrate a collection of tips for transferring data values back and forth between SQL Server and SAS via comma-separated-value (.csv) files with the help of SQL Server and SAS wizards for writing and reading .csv files. That approach is a good one for beginners for data transfers between SQL Server and SAS, but it is not rich in flexibility. Also, the reliance on SAS and SQL Server wizards makes the approach fundamentally manual, so it is difficult to maintain in a systematic way over time.

If you have several different types of transfers between SQL Server and SAS to manage that recur at periodic or aperiodic intervals, then it would definitely be useful to have code-based techniques that you could launch to initiate a transfer. Code-based techniques operate according to how they are written; there is no opportunity for human error. In addition, you or someone else who is a SAS developer can make changes to the data transfer process and be sure the changes will be followed. By adding comments to the code for a data transfer, you can simplify future maintenance of a data transfer process.

SAS/ACCESS and the LIBNAME Statement

SAS offers an ODBC driver and an add-on package (SAS/ACCESS) to Base SAS and other collections of SAS software for facilitating SAS interoperability with data values in other software, such as SQL Server. SAS/ACCESS comes in numerous different implementations depending on the other software with which you need to interoperate. As a SQL Server professional, you may want to consider the SAS/ACCESS for ODBC.

The SAS ODBC driver enables SAS code to access and manipulate ODBC compliant data sources. Because SQL Server is ODBC compliant, you can use the SAS ODBC driver to read from and write to SQL Server data from within a SAS application. The driver requires an ODBC DSN on the computer running SAS pointing at the SQL Server data source. The DSN can be a user, system, or file type.

Within the SAS application, you need a SAS/ACCESS LIBNAME statement referencing the ODBC DSN. The LIBNAME statement enables SAS code to reference a SQL Server instance, database, or schema as if it were a local SAS data source. A LIBNAME statement can take several different formats depending on how you are connecting and precisely to what you are connecting. The following statement illustrates typical features of a LIBNAME statement for connecting to a SQL Server data source.

LIBNAME YourLibRefName ODBC DSN='YourDSNName' schema=YourSchemaName;

YourLibRefName - serves as a local name for a data source; instead of referencing a local SAS data source the name references a remote SQL Server data source, such as a database
YourDSNName - is the name of the ODBC DSN that points at the SQL Server database
YourSchemaName - references a particular schema within a SQL Server database

Reading and Processing SQL Server Data with Classic SAS Code

When an organization is creating a custom SAS analysis package with its own analysts, it can be very convenient to read SQL Server data with SAS code. With an ODBC DSN and a LIBNAME statement, any SQL Server professional can empower SAS professionals to read and write to a SQL Server data source. You can readily control the data they access by placing tables in a separate SQL Server database schema to which the LIBNAME statement refers.

The following code example demonstrates how to enable SAS code to read SQL Server data.

The DSN argument in the LIBNAME statement references a system DSN name sqlsrv_nt that points at a SQL Server database with a table initially created in this tip
The table resides in the MSSQLTips schema of the database
The database is referenced through the SAS ODBC driver
The SAS name SQL references the SQL Server data source specified by the LIBNAME statement

The SAS PROC CONTENTS statement prints SAS meta data about the Table1_From_SS table in the SQL Server database referenced by the SQL SAS name. The SAS PROC PRINT statement displays the actual data values for all columns and all rows in the SQL Server Table1_From_SS table.

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips;
TITLE "Table1_from_SS Documentation with SAS code";
PROC CONTENTS DATA = SQL.Table1_From_SS;
PROC PRINT DATA = SQL.Table1_From_SS;
RUN;

The next SAS code listing shows a more advanced use of SQL Server data with SAS code.

This listing creates a SAS dataset named Table1_from_SAS_ACCESS in a local SAS library named MSSQLTip.
The code requires that the preceding LIBNAME statement still be in force. You can do this by running the sample after the preceding code or repeating the LIBNAME statement at the start of the following code listing.
The code retains a subset of the original SQL Server table columns for the SAS dataset (GUID_val and ID_val).
In addition, two new SAS variables, SumOfVar1ThruVar2 and AddVar1ToVar2 , are created from the original SQL Server table columns.

TITLE "Table1_from_SS subset and processed with SAS code";
DATA MSSQLTip.Table1_from_SAS_ACCESS
  (KEEP = GUID_val ID_val SumOfVar1ThruVar2 AddVar1ToVar2);
SET SQL.Table1_from_ss;
SumOfVar1ThruVar2 = SUM(OF Var1-Var2);
AddVar1ToVar2 = Var1 + Var2;
PROC CONTENTS DATA = MSSQLTip.Table1_from_SAS_ACCESS;
PROC PRINT DATA = MSSQLTip.Table1_from_SAS_ACCESS;
RUN;

The outcome of the PROC PRINT statement appears in the following screen shot. This code sample actually duplicates the functionality of a similar code sample in an earlier tip on SAS/SQL Server interoperability. The advantage of the code sample here is that you do not need to covert SQL Server to a .csv file and then import the .csv file into SAS in order to be able operate on SQL Server data from within a SAS package with SAS code.

The advantage of the code sample here is that you do not need to covert SQL Server to a .csv file and then import the .csv file into SAS in order to be able operate on SQL Server data from within a SAS package with SAS code.

Reading and Processing SQL Server Data with PROC SQL

In addition to using classic SAS code to read and process SQL Server data with a LIBNAME statement, you can also use PROC SQL statements. The SAS SQL procedure accepts ANSI SQL statements along with custom SAS extensions. Therefore, a SQL Server professional who already is comfortable with T-SQL may find the creation of ANSI SQL statements in a PROC SQL statement a more natural extension of existing skills than using traditional SAS code to process SQL Server data.

The following PROC SQL illustrates some basic syntax issues.

You can use a libref name in a LIBNAME statement to refer to a SQL Server data source
The keywords PROC SQL enable a batch of SQL statements until the quit keyword which returns control to the interpreter for classic SAS statements
You can use SELECT * to select all columns from a data source
When there is no WHERE clause all rows from a data source are returned
If you want a certain number of rows from the beginning of a SQL Server result set, then use the OBS keyword instead of the TOP keyword

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips;
PROC SQL;
SELECT *
FROM SQL.Table1_from_SS;
* Replace TOP keywords with OBS argument in FROM clause;
PROC SQL;
SELECT *
FROM SQL.Table1_from_SS (OBS=2);
quit;

The next PROC SQL statement contains the syntax for an INNER JOIN between a table initially created in SQL Server (Table1_from_SS) and a second table created in SAS and then subsequently transferred to SQL Server (ForTable1_from_SAS). Notice the INNER JOIN syntax is identical to T-SQL. The part2 article illustrates the process for creating a table in SAS and transferring it to SQL Server with .csv files.

PROC SQL;
SELECT t1.ID_val, t1.Var1, t1.Var2, t2.SumOfVar1ThruVar2
FROM SQL.Table1_from_SS t1
 INNER JOIN SQL.ForTable1_from_SAS t2
  ON t1.ID_val = t2.ID_val;
quit;

If a SAS analysis package successively processes the same set of rows multiple times, it will typically be more efficient to create a local SAS dataset instead of refer to the same column values repeatedly through an ODBC connection. The benefit of the local SAS connection grows as the number of rows grows from several hundred or thousand rows to multiple hundreds of thousands rows or more. In addition, you might find creating a local SAS dataset mandatory when your prime objective is to import SQL Server data for analysis by a SAS package prepared by an outside organization that requires a SAS dataset for input.

The CREATE TABLE statement used with a SELECT statement within a PROC SQL statement can enable the creation of a local SAS dataset based on one or more SQL Server tables. The following sample illustrates the basic syntax.

The LIBNAME statement specifies the source database with library members being tables within a specified schema (MSSQLTips) for a database pointed at by a DSN schema.
The SELECT statement designates the data from SQL Server that you want transferred to a local SAS dataset.
The CREATE TABLE statement, which precedes the SELECT statement, specifies a SAS library name, MSSQLTip, and a member name, Table1_from_SS_from_create_table, within the library to store the data from SQL Server.

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips;
PROC SQL;
CREATE TABLE MSSQLTip.Table1_from_SS_from_create_table AS
SELECT *
FROM SQL.Table1_from_SS;
quit;

Next Steps

This article is to introduce you to basics of using SAS/ACCESS features using SQL Server data inside a SAS analysis package -- either custom developed at your organization or a standard package used at multiple organizations across the globe. Because the goal is to just introduce you to the topic, there is a lot more to learn. Use the following links as starting points for growing your knowledge of how to apply SAS/ACCESS as a tool for growing how you use SQL Server data inside of SAS analysis packages.

This link provides a general introduction to SAS/ACCESS with relational and non-relational data sources
This link offers step-by-step screen shots for creating an ODBC DSN for a SQL Server data source and referencing the DSN with a LIBNAME statement
This last link demonstrates a variety of programming samples for the PROC SQL statement
Review Part 1 and Part 2 of this series
- Transferring data from SAS to SQL Server and back
- Using Wizards to Export a Dataset from SAS to SQL Server

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Monday, June 23, 2014 - 4:27:23 PM - wes	Back To Top (32357)
You might mention schema=ALLCAPS. Just found that - it seems to allow proc contents work with the libname statement. (https://communities.sas.com/message/205335). Although I only seem to be getting "Tables" in proc contents (not VIEWS, etc) at present.