BookmarkSubscribeRSS Feed
goliPSU
Calcite | Level 5

Hi,

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

Thank you.

5 REPLIES 5
TomKari
Onyx | Level 15

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
Calcite | Level 5

Thank you so much.

naeemomer
Calcite | Level 5

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
Super User

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?

BASUG is hosting free webinars Next up: Mark Keintz presenting History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies on May 8. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASKiwi
PROC Star

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Discussion stats
  • 5 replies
  • 5914 views
  • 0 likes
  • 5 in conversation