The SAS-code below shows you how to make SQL pass-through to DB2 and collect the result in a new table.
proc sql noprint;
connect to db2 as db(database=<Your database> user=<Your username> password=<Your password>);
create table <Dummy> as
select * from connection to db
(
select * from <schema>.<table>
);
disconnect from db;
quit;
It is also possible to use authdomain to collect credentials instead of user and password and the DB2 database has to be available to SAS.
The SAS code below shows you how to make SQL DB2 pass-through.
proc sql noprint;
connect to db2 (database=<Your database> user=<Your username> password=”<Your password>”);
execute
(
<Your SQL sentence>
) by db2;
disconnect from db2;
quit;
If you have your credentials stored in a SAS metadata-server it is possible to use a authdomain and skip entering username and password.
It is done using the following option in SAS:
options
metaserver=”<Your metadataserver>”
metaport=<usually 8561>
metaprotocol=bridge
metarepository=”Foundation”
;
proc sql noprint;
connect to db2 (database=<Your database> authdomain=<Your authdomain>);
execute
(
<Your SQL sentence>
) by db2;
disconnect from db2;
quit;
The database has to be available to SAS. This can eg be done through your DB2 ControlCenter.
The SAS libname statement below shows you how to make an ODBC-connection to an SQL-server through ODBC without first having to create the ODBC-connection in the OS.
libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; TRUSTED_CONNECTION=yes" schema=<Name of schema in database> access=readonly;
‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.
‘access=readonly‘ is done for precaution. If the user is only suppose to read the data then this should be set on the datasource – not on the libname.
It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.
libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; UID=<name of user>; PWD=<Password for user>" schema=<Name of schema in database> access=readonly;
It’s also possible to make SQL pass-through without having an ODBC-connection in the operating system (OS).
proc sql noprint;
connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; TRUSTED_CONNECTION=yes");
execute ( <Your SQL sentence> ) by odbc;
disconnect from odbc;
quit;
‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.
It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.
proc sql noprint;
connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; UID=<Your username>; PWD=<Your password>");
execute ( <Your SQL sentence> ) by odbc;
disconnect from odbc;
quit;
It’s also possible to make a pass-through connection using an DSN created eg in the ‘Data Sources (ODBC)’ in Windows.
proc sql noprint;
connect to odbc (DSN=<Your DSN name> USER=<Your username>; Password=<Your password>);
execute (
<Your SQL sentence>
) by odbc;
disconnect from odbc;
quit;
Below is a link to an article from SAS explaining different ways of using SAS ODBC-connection directly in SAS . It stats that NOPROMT can’t be used with the SQL Server ODBC Driver, but I haven’t experienced that.
Usage Note 52777: Examples of SAS/ACCESS® Interface to ODBC LIBNAME code used to access a Microsoft SQL Server database without configuring an ODBC data source name
http://support.sas.com/kb/52/777.html