write the SAS results into a SQL Server table

[goliPSU]

Hi,

I would like to know if anyone knows how to export data from SAS to SQL Server?

Thank you.

[TomKari]

If it is a small quantity of records (<10,000):

1. Define your SQL Server database and schema as a SAS library, using SAS/Access (I assume you've already done this).

2. Use either data step or proc sql:

libname sqllib ...;

/* data step */

data sqllib.newdata;

set work.olddata;

run;

/* proc sql */

proc sql;

create table sqllib.newdata as

select * from work.olddata;

quit;

I don't have access to SAS in my environment, so there may be some syntax errors. But is should be close. Of course, SAS being SAS, there are many other ways to do it.

The 10,000 record limit is somewhat arbitrary, based on my experience. If the above option is too slow, there is an option to use the SQL Server bulk loader. I've never used in in SQL server, but in another environment the bulk loader was 100 to 1000 times faster.

Tom

[goliPSU]

Thank you so much.

[naeemomer]

Thank you for posting the solution, as I was looking for it as well. 

I just want to confirm, if following SAS Syntax to define SQL Server database through ODBC. I am asking, as I get error to deploy the given solution.

libname sqldatabase odbc user=Testuser password=Passw0rd dsn= SQLMLserver   Connection=Shared;

[Quentin]

This depends on your environment.  To use the ODBC engine, you must have SAS/ACCESS licensed, and also you must set up the ODBC connection outside of SAS.  If you are working on a a SAS serve (vie EG or SAS Studio), often an admin must set up the ODBC connection for you. If you test the SQLMLserver dsn outsider of SAS, does it work?  Can you show the error message you get from SAS?

[SASKiwi]

Your LIBREF must be no more than 8 characters - sqldatabase. Shorten it to just SQLDATA and see how you get on.