Posted By

webonomic on 03/31/08

Tagged

Versions (?)

Last Edited at 03/31/08 02:57pm

Statistics

Viewed 980 times

Favorited by 0 user(s)

Related snippets

Connecting to Microsoft SQL Server from SAS with OleDB

/ Published in: SAS

Highlighted TextExpand

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;

/*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;

/*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;

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.