topic Re: Push Dataset to MS SQL server in SAS Programming

Push Dataset to MS SQL server

monona — Sat, 23 Feb 2019 00:21:35 GMT

Hello,

I am wondering if there is any other way to export sas dataset to ms sql server other than sas_dataset>csv>ms sql dataset manually.

I connected to the MS sql server through

libname ABC odbc noprompt = "server=SQLSVR;driver=SQL server native client 11.0; 
        Trusted_connection=yes" database=mydatabase schema=dbo;

However, i haven't understood how this works.

I would appreciate any recommendations/tips regarding this problem.

Re: Push Dataset to MS SQL server

VDD — Sat, 23 Feb 2019 00:37:05 GMT

A couple of suggestions:

Use SAS to load a temporary SQL Server table which is then used by the SQL Server load process.
Write a delimited file from SAS then get the SQL Server load process to read the delimited file

https://communities.sas.com/t5/SAS-Data-Management/Load-SAS-Dataset-to-MS-SQL-Server-via-SQL-server-job/m-p/290472#M8243

us the update statement in you datastep or sql statement.

Re: Push Dataset to MS SQL server

Patrick — Sat, 23 Feb 2019 00:40:59 GMT

@monona

What do you mean by "manually"? Once you've defined the library connecting to the data base you can use it like any other libref.

Code like below would append data from sashelp.class to a table class on your server. It even would create the table in the database if it doesn't exist already.

proc append base=abc.class data=sashelp.class;
run;

The SAS/Access to ODBC documentation will provide you with much more detail.