There is a great tool from Savian that will help generate code to copy SAS Datasets to a MSSQL Server. You can get it from http://www.savian.net/utilities.aspx. Here is some sample code it generates:

libname SASData ‘C:\PathToSASfiles\DataFolder’;
libname SQLSrvr oledb provider=sqloledb init_string=’Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myusername Catalog=my_database_name;Data Source=database_server_name’ schema=dbo ;
options fmtsearch=(SASData) ;

data SQLSrvr.my_data;
set SASData.my_sas_dataset ;
run;

How easy is that?

Note that some SQL Servers run as “Instances” so your Catalog could be something like: DatabaseServer\InstanceServer and your connection string would reflect just that:

libname SQLSrvr oledb provider=sqloledb init_string=’Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myusername Catalog=my_database_name\my_instance_name;Data Source=database_server_name’ schema=dbo ;  

What if you want to get the data out of the database and into the SAS environment? Easy, just do the opposite in your datastep: 

data SASData.the_data;
set SQLSrvr.table_name;
run;
 

One problem you may run into is, how do I get a listing of my tables? Well, you can get a listing of your tables with this code:

Your database administrator may be able to give you a diagram layout of the database. This comes in handy for quick references to table names and column names, as well as data types. I create the diagram myself by using a tool called DbVisualizer. They offer a free-for-personal-use license. Read my other post on how to use this tool.

You can get a listing of the tables by using this code from SAS Samples #1529:

proc sql;
connect to odbc(dsn=sqlserver uid=dbitest pwd=dbigrp1);
create table list1
as
select * from connection to odbc(ODBC::SQLTables);
create table list2
as
select * from connection to odbc(ODBC::SQLColumns,,”DEPT”,);
create table list3
as
select * from connection to odbc(ODBC::SQLColumns,,”DEPT”,”DNAME”);
quit;
 

As you can see, list1 will give you all of the tables in the database while list2 gives you the columns of table “DEPT”. Don’t forget, if you are using OleDB, change the above ODBC to OLEDB.