Have you ever needed to transfer data from a SAS dataset to a SQL Server table? Whether you’re working with external organizations, internal SAS developers, or running SAS analytic packages, there are multiple approaches to handle this data transfer. In this article, we will explore one specific approach that utilizes SAS/ACCESS, PROC SQL, and ANSI SQL.

The first step in transferring data from SAS to SQL Server is to have a SQL Server destination table ready to receive the data. SAS/ACCESS and PROC SQL allow you to create the table on the fly, ensuring that it conforms to the structure of the SAS dataset. This approach simplifies the process by creating the SQL Server destination and depositing the data from SAS in a single step.

Here is an example of how to create a SQL Server table from a SAS dataset using the CREATE TABLE/SELECT approach:

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;

In the above example, the LIBNAME statement specifies the ODBC DSN and schema for the SQL Server destination. The PROC PRINT statement is optional and is used to display the values in the SAS dataset before exporting to SQL Server. The CREATE TABLE statement assigns a name to the SQL Server table and the SELECT statement specifies the values from the SAS dataset to be transferred.

If the SQL Server table already exists, you can manage its contents by either appending new data or emptying the table before inserting fresh data from SAS. Here is an example of how to manage the content of an existing SQL Server table from SAS:

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips;

/* Insert 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;

In this example, the INSERT/SELECT statements are used to insert selected rows from the SAS dataset into the SQL Server table. The DELETE statement is used to empty the table before inserting a fresh batch of rows. This approach is useful when you need to select different subsets of data from a current set of values in a SAS dataset or when you want to track values generated sequentially.

It’s important to note that SAS/ACCESS and its associated ODBC connection do not support the TRUNCATE TABLE statement. Instead, you must use the DELETE statement to empty the SQL Server table. However, if the logging for the DELETE statement has a substantial performance impact, you may consider using a .csv file to transfer the data instead.

By utilizing SAS/ACCESS, PROC SQL, and ANSI SQL, you can easily transfer data from SAS to SQL Server. Whether you’re creating a new SQL Server table or managing the content of an existing table, these tools provide a seamless and efficient solution for data transfer.