Communication between SAS and DBMS (sqlserver)

1 Use oledb

Code description: library copy the testtable table to the local SAS work logical database

Libname mydb oledb init_string = " Provider = sqloledb.1; Password = ******; persist Security info = true; user id = sa; initial catalog = testdb; Data Source = 192.168.0.3 " ;

Data testtable;

Set Mydb. testtable;

Run;

Proc print;

Run;

2 use odbc engine

Code Description: Replace the testdb data on the 192.168.0.3 ServerLibraryCopy the testtable table to the work logical database of the local SAS.

First, create ODBC Engine" Sastest "

Libname mydb ODBC uid = Sa pwd = ****** Database = Testdb DSN = Sastest;

Data testtable;

Set Mydb. testtable;

Run;

Proc print;

Run;

3Database link wizard

Code Description: Open the database link wizard

Libname mylib oledb;

% Put & Sysdbmsg ;( % Put % Superq (sysdbmsg); sasv9 is better)

Data bpsas;

Set Mylib. BP;

Run;

Proc print;

Run;

4 Link SQL Server Use NT Verify

Code Description: Set Sashelp. Class Table upload SQL Server Upper

Libname x oledb provider = Sqloledb DSN = ' Sastest ' Properties = ( " Integrated Security " = Sspi " Persist Security info " = True

" Initial catalog " = Northwind );

Proc SQL;

Create Table X. Class As Select * From sashelp. Class Where Age > 10 ;

Quit;

5 Call database Stored Procedures

LReturned value. Stored Procedure name: usp_who

Proc SQL;

Connect to oledb (DSN = ' Sastest ' UID = As PWD = ****** Provider = Sqloledb );

Select * From connection to oledb (usp_who );

Quit;

L Stored Procedure name without return value: sp_rename Proc SQL;

Connect to oledb (DSN = ' Sastest ' UID = As PWD = ****** Provider = Sqloledb );

Execute (sp_rename " Badcrime " , " Barbcrime " ) By oledb;

Quit;

6 accessing OLAP data (Access Online Analytical Processing Data) Proc SQL;

Connect to oledb (provider = Msolap props = ( ' Initial catalog ' = ' Foodsmart 2000 '

' Data Source ' = ' Dwtsrv1 ' ));

Select * From connection to oledb

(MDX: select non empty {[Time]. [1997], [Time]. [1998]} On columns, non

Empty {[Account]. [all account]} On rows from [budget] Where ([category].

[All category], [measures]. [amount], [store]. [All stores]);

Quit;