Advanced Search

Connecting to Microsoft SQL Server from SAS with OleDB

/ Published in: SAS
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. libname SASData ‘C:\PathToSASfiles\DataFolder’;
  2.     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 ;
  3.     options fmtsearch=(SASData) ;
  4.  
  5.     data SQLSrvr.my_data;
  6.     set SASData.my_sas_dataset ;
  7.     run;
  8.  
  9. /*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:*/
  10.  
  11.     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 ;  
  12.  
  13. /*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: */
  14.  
  15.     data SASData.the_data;
  16.     set SQLSrvr.table_name;
  17.     run;
  18.  
  19. /*You can get a listing of the tables by using this code from SAS Samples #1529:*/
  20.  
  21.     proc sql;
  22.     connect to odbc(dsn=sqlserver uid=dbitest pwd=dbigrp1);
  23.     create table list1
  24.     as
  25.     select * from connection to odbc(ODBC::SQLTables);
  26.     create table list2
  27.     as
  28.     select * from connection to odbc(ODBC::SQLColumns,,”DEPT”,);
  29.     create table list3
  30.     as
  31.     select * from connection to odbc(ODBC::SQLColumns,,”DEPT”,”DNAME”);
  32.     quit;

URL: http://jaredprins.squarespace.com/blog/2008/3/31/connecting-to-microsoft-sql-server-from-sas-with-oledb.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.