Connecting to Microsoft SQL Server from SAS with OleDB


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



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.