Using SAS ACCESS and PROC SQL to Save SAS Data in SQL Server

By: Rick Dobson   |   Updated: 2012-10-05   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Other Database Platforms

Problem

Have you been tasked with transferring data from a SAS dataset supplied by an outside organization to a SQL Server table? Do you have internal SAS developers creating results in SAS datasets that need to be stored in SQL Server? Are you or others in your organization running a SAS Analytic package that saves its results in a SAS dataset, but which ultimately needs to be stored in SQL Server?

Solution

There are at least two approaches to handling the transferal of data from a SAS dataset to a SQL Server table. One solution is to export the data from SAS as a .csv file and then import the .csv into a SQL Server table. Some illustrative ways of implementing data transfers between SAS and SQL Server via .csv files were introduced in a prior pair of MSSQLTips articles (Transferring data from SAS to SQL Server and back and Using Wizards to Export a Dataset from SAS to SQL Server).

This article highlights another approach that is especially well suited to cases in which you are regularly transferring data between SQL Server and SAS. This second approach relies on SAS/ACCESS, PROC SQL, ANSI SQL. These tools were previously described in MSSQLTips by the following article Using SAS ACCESS and PROC SQL to Retrieve SQL Server Data.

When preparing to transfer data from SAS to SQL Server via SAS/ACCESS, you must have a SQL Server destination table into which to deposit SAS data. SAS/ACCESS and PROC SQL permit you to create the table on the fly so that it conforms to an existing SAS dataset. With this approach, you create the SQL Server destination and deposit data from SAS in the SQL Server with a single step. Another strategy is to manage the contents of existing SQL Server destination. With this second type of solution, you can append SAS data to an existing SQL Server table or you can empty an existing SQL Server table before depositing fresh data from SAS into the table.

Creating and Populating a SQL Server Destination from SAS

You can create a SQL Server table from SAS that conforms to the result set from an ANSI SQL SELECT statement in SAS. The solution requires a SAS/ACCESS LIBNAME statement that points at a destination within SQL Server. This kind of LIBNAME statement works through an ODBC driver; see the Using SAS ACCESS and PROC SQL to Retrieve SQL Server Data tip for more detail on these elements of the solution. Within a PROC SQL statement in a SAS program, you need a CREATE TABLE statement followed by a SELECT statement.

• The CREATE TABLE statement assigns a name to the SQL Server table at the location specified by the LIBNAME statement.
• The result set from the SELECT statement is passed to the table in SQL Server

This approach assumes the table referenced in the CREATE TABLE statement does not already exist in SQL Server. If the table already exists, the CREATE TABLE statement fails and the process aborts. If you must re-use a previously existing SQL Server table, consider another approach -- see the next section for an example of how to implement the alternative approach.

The following script demonstrates how to use the CREATE TABLE/SELECT approach in SAS for creating and populating a table in SQL Server.

• The LIBNAME statement at the top of the script points at the MSSQLTips schema in the database referenced by the sqlsrv_nt ODBC DSN.
• The PROC PRINT statement is not essential to the solution; it merely displays the SAS dataset values that will populate a SQL Server table.
• The CREATE TABLE statement designates ReturnedTable1_from_sas_access as the name of the SQL Server table to be populated with data. Notice that an AS keyword follows the table name.
• The SELECT statement after the AS keyword specifies the values from the Table1_from_sas_access dataset in a SAS library named MSSQLTip. The dataset values populate the ReturnedTable1_from_sas_access table in SQL Server.

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips;
/* Print dataset before exporting to SQL Server */
PROC PRINT Data=MSSQLTip.Table1_from_sas_access;
run;
/* PROC SQL example to create a SQL Server table
from a SAS dataset */
PROC  SQL;
CREATE TABLE SQL.ReturnedTable1_from_sas_access AS
SELECT *
FROM MSSQLTip.Table1_from_sas_access;
quit;

The following screen shot displays the data form the PROC PRINT statement in the preceding SAS program. SAS has just two data types: numeric and character. All numeric data are represented internally in SAS with a floating point data type, but numeric values can display as integer values depending on the value and formatting applied to a value. Missing or NULL numeric values have a default representation in SAS of ".".

• The first and fourth rows for the AddVar1ToVar2 variable in the Table1_from_sas_access SAS dataset have missing values.
• The values in the ID_val column appear without a decimal point because SAS recognizes that these values have precise integer representations.
• The GUID_val column values, which originated from SQL Server, have a character SAS data type. There is no uniqueidentifier data type in SAS.

After SAS executes the code within the PROC SQL statement from the preceding script, it creates and populates the ReturnedTable1_from_sas_access table in SQL Server. The following screen displays the Object Explorer, Query, and Results panes from SQL Server Management Studio displaying values and meta data for the table. SAS automatically transfers all the ID_val column values in an integer data type (numeric(11,0)). Although the GUID_val column values originated with a uniqueidentifier type in SQL Server before they were transferred to SAS, they are not returned from SAS as uniqueidentifier values. Instead, GUID_val column values have a varchar data type. Also, the SAS "." representation for missing AddVar1ToVar2 values is converted to a NULL value.

Managing the Content of a Previously Existing SQL Server Table from SAS

The preceding solution is convenient the first time that you have to transfer values from a SAS dataset to a specific SQL Server table. The CREATE TABLE statement used in combination with a SELECT statement creates and populates a SQL Server table from a SAS dataset, but the CREATE TABLE statement fails the second time you use it; recall that the failure is because of a name conflict with the table created on the initial use of the CREATE TABLE statement.

You can work around the first-and-only-use problem with CREATE TABLE/SELECT statement by referencing a previously existing SQL Server table. Then, you can either

• insert and append rows to the existing table or
• clear the table prior to inserting new rows in order to dump fresh rows into the table

The following code sample demonstrates how to create a SQL Server table from SAS. Notice that you can use a CREATE TABLE statement in a PROC SQL statement within SAS very similarly to the way you use a CREATE TABLE statement in the query pane of SQL Server Management Studio. The num keyword after the Var1 and Var2 columns specifies a data type suitable for numeric values. The char keyword after the Name, Var3, and Comment columns designates a data type for character values; the number in parentheses after char indicates the maximum length of the character value. As a SQL Server professional, you will probably feel more comfortable creating SQL Server tables in SQL Server, but if you are working with SAS professionals or you are required to use SAS extensively, a working knowledge of how to create SQL Server tables from SAS may have value for you.

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips; 
/* Code to create an empty SQL Serter table; */
PROC SQL; 
CREATE TABLE SQL.Table_for_SS_from_SAS_Access 
(Name char(50), 
Var1 num, 
Var2 num, 
Var3 char(10), 
Comment char(100)); 
quit; 

The next SAS script shows the code to populate the SQL SERVER table created by the preceding script in two different ways. First, the script initially transfers a subset of rows from the Table1_from_SS_from_Create_Table SAS dataset in the MSSQLTip library. This SAS dataset was initially created for the Transferring data from SAS to SQL Server and back tip. The columns of the Table_for_SS_from_SAS_Access table are a subset of those in the Table1_from_SS_from_Create_Table SAS dataset.

• Notice that the script designates the transfer rows of data from a SAS dataset to a SQL Server table with a SELECT statement following an INSERT statement inside a PROC SQL statement.
• The first INSERT/SELECT statement combination inserts a subset of rows from the Table1_from_SS_from_Create_Table SAS dataset. Since the table was just created, the rows are inserted into an empty table.
• The second INSERT/SELECT statement appends to the SQL Server table a different subset of rows from the Table1_from_SS_from_Create_Table SAS dataset.
• After each INSERT/SELECT statement, a SELECT statement displays the contents of the SQL Server table.

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips; 
/* Insert of selected rows */
PROC SQL; 
INSERT INTO SQL.Table_for_SS_from_SAS_Access
SELECT Name, Var1, Var2, Var3,Comment
FROM  MSSQLTip.Table1_from_SS_from_Create_Table
WHERE Var1 IS NULL OR Var2 IS NULL;
TITLE Values in SQL table after insert of selected rows;
PROC SQL;
SELECT *
FROM SQL.Table_for_SS_from_SAS_Access;
/* Insert/append of different selected rows */
PROC SQL; 
INSERT INTO SQL.Table_for_SS_from_SAS_Access
SELECT Name, Var1, Var2, Var3,Comment
FROM MSSQLTIP.Table1_from_SS_from_Create_Table
WHERE Var1 IS NOT NULL AND Var2 IS NOT NULL;
TITLE Values in SQL table after insert/append of different selected rows;
PROC SQL;
SELECT *
FROM SQL.Table_for_SS_from_SAS_Access;
quit; 

To reinforce your understanding of the preceding code sample, the following screen shot shows the original SAS dataset values followed by the transferred values to the SQL Server table. After the first INSERT/SELECT statement, two rows transfer from the SAS dataset to the SQL Server table. The transferred rows have missing values for either Var1 or Var2. After the second INSERT/SELECT statement, the remaining three rows from the SAS dataset append to the table after the initial two rows. This approach to populating a SQL Server table from SAS is appropriate for cases where you will be selecting different subsets of data from a current set of values in a SAS dataset or when you successively run a SAS package and you want to track values generated sequentially.

Another common scenario for transferring values from SAS datasets to SQL Server is to save the current values of a SAS dataset in an empty SQL Server table. This type of requirement calls for the most recently transferred SAS values to go to an empty SQL Server table no matter how many previous batches of values were transferred to the same table. The solution here is obvious. Empty the SQL Server table before populating the SQL Server table. As a SQL Server professional, you may think that the most obvious approach is to use a TRUNCATE TABLE statement to empty the SQL Server table. However, SAS/ACCESS and its associated ODBC connection does not implement this statement. Therefore, you must instead use a DELETE statement. The logging for the DELETE statement can affect performance. As a consequence, you may prefer to return values from SAS to SQL Server via a .csv file. If your dataset is not so large that the logging has a substantial performance impact, the approach may still be worthwhile because it is straightforward and easy to implement.

The following script starts by inserting selected rows to the Table_for_SS_from_SAS_Access SQL Server table. This beginning code segment is just to confirm that there are values in the table before inserting another fresh batch of rows. To ensure the next batch of rows is fresh for the table, a DELETE statement follows the initial INSERT/SELECT statement. After the DELETE statement, a second INSERT/SELECT statement inserts a different batch of rows into the table. Both INSERT/SELECT statements are discussed in the preceding demonstration for inserting/appending rows. After the sample code concludes running, the Table_for_SS_from_SAS_Access SQL Server table should contain just the three rows from the second INSERT/SELECT statement. The screen shot after the code sample confirms this outcome.

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips; 
/* Insert of selected rows */
PROC SQL; 
INSERT INTO SQL.Table_for_SS_from_SAS_Access
SELECT Name, Var1, Var2, Var3,Comment
FROM MSSQLTIP.Table1_from_SS_from_Create_Table
WHERE Var1 IS NULL OR Var2 IS NULL;
quit;
/* Empty Table */
PROC SQL;
DELETE FROM SQL.Table_for_SS_from_SAS_Access;
quit;
/* Insert fresh batch of different selected rows */
PROC SQL; 
INSERT INTO SQL.Table_for_SS_from_SAS_Access
SELECT Name, Var1, Var2, Var3,Comment
FROM MSSQLTIP.Table1_from_SS_from_Create_Table
WHERE Var1 IS NOT NULL AND Var2 IS NOT NULL;
TITLE Values in SQL table after insert of fresh batch of different selected rows;
SELECT *
FROM SQL.Table_for_SS_from_SAS_Access;
quit; 

Next Steps

This article is the fourth in a series on transferring data between SQL Server and SAS. The objective of all four articles is to describe and demonstrate approaches that are relatively easy for SQL Server professionals to understand -- even if they have little or no prior exposure to SAS. The first two articles described how to transfer data via .csv files. This is the second of two articles that illustrates another approach to transferring values based on SAS/ACCESS, its associated ODBC driver, and ANSI SQL inside PROC SQL statements. Both approaches have advantages and disadvantages.

• The .csv file approach is relatively easy to start using and well suited for those with infrequent transfer requirements because you require no special software other than SQL Server and SAS. For those who prefer graphical solutions, no code is required for this approach; you can implement the data transfer with wizards.
• The approach based on SAS/ACCESS and related technologies is appealing because you can program it with ANSI SQL which should be easy for SQL Server professionals to learn who enjoy programming T-SQL. Unfortunately, SAS/ACCESS is an add-on package to Base SAS that requires an extra license from SAS. The ease of use SAS/ACCESS enables may well be worth the cost of its license -- especially if you regularly have to transfer data from SQL Server to SAS.
• My experience is that the performance of transferring values from SAS to SQL Server via SAS/ACCESS is not always favorable relative to using .csv files.
• Furthermore, you can automate the .csv approach, but you end up with custom code that has its own debugging requirements and maintenance issues as you apply the approach to different SAS/SQL Server transfer applications.
• Adapt whatever helps you from this series on transferring data between SQL Server and SAS. Experiment with the approaches to find what works best for you or use the series as a springboard to adopting other techniques beyond those discussed in this series.

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