Advanced Search

Return to Snippet

Revision: 5742
at March 31, 2008 14:57 by webonomic
Initial Code 
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;
Initial URL 
http://jaredprins.squarespace.com/blog/2008/3/31/connecting-to-microsoft-sql-server-from-sas-with-oledb.html
Initial Description 

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Connecting to Microsoft SQL Server from SAS with OleDB

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SAS