Connecting to Azure SQL-server from SAS

This post shows how it is possible to connect to an SQL-server server/database (PaaS) in Windows Azure from SAS.

First of all you need to create a SQL-server database. When you create an SQL-server database it will also create a virtual server.The servername for the virtual server is the “funny” name eg. aoe8kg2q9w.
It is on this server, that you need to open for the different IP that need access to the SQL-server.

As shown below you go to the Servers-tab and click on the server where you want to change the firewall settings.

AzureServers

On the server you go to the Configure-tab and enter the IP-adress that has to access the SQL-server.

IPAccess

It is possible to get different connection strings for your SQL-database on Azure. As shown below you need to choose ‘View SQL Database connection strings’.

ConnStrings

Here you can eg find the connection string for an ODBC connection and use it in your libname statement for SAS.
Now you can connect through SAS. First you make a libname. This has only worked for me when I use one of the new ODBC-drivers for Microsoft SQL-server eg. ODBC Driver 11 for SQL Server.

libname azure odbc NOPROMPT=”Driver=ODBC Driver 11 for SQL Server;Server=tcp:<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Uid=<USERNAME>@<SERVERNAME>;
Pwd=<PASSWORD>;Encrypt=yes;Connection Timeout=30;” schema=<SCHEMA>;

Below I have made an example. Where I copy sashelp.class to my Azure database.
I need to create an identical copy of sashelp.class on Azure.

proc sql;
create table azure.class like sashelp.class;
quit;

I need this copy because Azure needs a clustered index on the table. If you don’t have a clustered index on the table, it is not possible to load data into the table.

proc sql noprint;
connect to odbc  (NOPROMPT=”Driver=ODBC Driver 11 for SQL Server;Server=tcp:<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Uid=<USERNAME>@<SERVERNAME>;
Pwd=<PASSWORD>;Encrypt=yes;Connection Timeout=30;”);

execute  (
CREATE CLUSTERED INDEX idxName ON [SCHEMA].[class]   (    [Name] ASC   )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) by odbc;
disconnect from odbc;
quit;

Now that I have made the clustered index on the table. It is possible to load data into the table.

proc sql;
insert into azure.class
select * from sashelp.class;
quit;

Start folder for SAS

It is possible to set the start folder for SAS in different ways. First of all you can do it in the shortcut to SAS in your operating system. This is done by setting the -SASINITIALFOLDER as shown below.

SASInitialFolder

 

 

 

 

 

 

 

 

 

 

 

 

It is also possible to make changes to your sas9v.cfg file. Where you eg. insert the statement -SASINITIALFOLDER “F:\Projects”

Parallel execution in SAS

The example below shows one way to do parallel execution of SAS sessions. In the example below two SAS sessions is started in parallel and a third is executed depending on the result of the two session.

Gets the path of the main SAS program that is executed.

%macro GetSASProgramPath;
 %qsubstr(%sysget(SAS_EXECFILEPATH),1,%length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname))-1);
%mend;

 %let SASProgramPath = %GetSASProgramPath;
%put &SASProgramPath;

Starts the first SAS session. %syslput makes it possible to give the SAS session a macrovariable from the main program. In this case it is the path for the SAS program being executed.
wait=no tells SAS not to wait for the SAS session to end before starting the next SAS session.
%sysrput makes it possible to return a macrovariable from a SAS session to the main SAS program. In this cause it whether or not the SAS session executed with og without errors.

option sascmd = “sas”;
signon remote = OneSes;
%syslput _SASProgramPath = &SASProgramPath;
rsubmit process = OneSes wait=no;
 %include “&_SASProgramPath\<SOME PROGRAM.SAS>”;
 %sysrput FirstSessionError = &syscc;
endrsubmit;

Starts the second SAS session.

option sascmd = “sas”;
signon remote = TwoSes;
%syslput _SASProgramPath = &SASProgramPath;
rsubmit process = TwoSes wait=no;
%include “&_SASProgramPath\<SOME PROGRAM.SAS>”;
%sysrput SecondSessionError = &syscc;
endrsubmit;

The statement below waits for the two sessions above to finnish and executes a SAS program if the two sessions did not fail.

waitfor OneSes TwoSes;
signoff OneSes;
signoff TwoSes;
%macro FinalRun;
%if &FirstSessionError < 5 and &SecondSessionError < 5 %then %do;
%put –== No Errors ==–;
%include “&_SASProgramPath\<SOME PROGRAM.SAS>”;
%end;
%mend;
%FinalRun;

Inserting quoted string in where sentence

There are different possibilities when you need to quote a string in SAS.

Before you read any further! Instead of using ‘ ‘ around the macro-variable use “ “.

The SQL-statement below will not work.

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = ‘&Year.’;
quit;

The SQL-statement below will work.

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = “&Year.”;
quit;

If you have a macro variable containing a year that could be 2012, and you need to put ‘ ‘ or “ “ around it, you can use the macro-functions %bquote as shown below.

My program works like this. I retrieve a year variable into a macro-variable from a configuration dataset.

proc sql noprint;
select upcase(Value) into :Year
from Config.Config_data
where upcase(Name) = upcase(‘Year’);
quit;

I remove blanks (if any)

%let Year = %trim(&Year.);

When I tried to quote the macro-variabel Year (it’s a string not a numeric) to get Year = ‘2012’ I got and error

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = %bquote(‘&Year.’);
quit;

I found out that I needed to trim the %bquote-statement again. When I did that it worked.

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = %trim(%bquote(‘&Year.’));
quit;

Another way to solve this would be to use

proc sql noprint;
select upcase(Value) format=$quote40. into :Year
from Config.Config_data
where upcase(Name) = upcase(‘Year’);
quit;

This will put “” around the year like “2012”.

How to install and configure DB2 ODBC driver

The following shows you how I got an ODBC connection to DB2 on a Windows Server 2008R2. After doing a search on the Internet I found this page that really helped me a lot. They do it a bit different.

NEW
If you also want to install the IBM Control Center you might want to look at this page. This also installs the ODBC out-of-the-box.
IBMCS
If your need a 32 or 64-bit version for Windows and DB2 version 9.7. You should look for something like this.
IBMDB2CS

On this page you can find and download the ODBC-drivers that fits your system. I you don’t know what version of DB2 that you’re running, you can contact your DBA. The DB2-version can be found using the command DB2level.
IBM homepage

Choose either ODBC 32-bit or 64-bit for your DB2-version and download it for Windows.

If your need a 64-bit version of the ODBC-driver for Windows and DB2 version 9.7. You should look for something like this.
IBM download

NB! You need to create an IBM-account to download the file.

Copy the content of ZIP-file containing the ODBC-driver that you just downloaded to c:\program files\IBM. The path is optional, you can choose a different path if you want to or are using a 32-bit ODBC-driver. The files and folders in c:\program files\IBM, should look something like this.
Dirlist

Start a Windows command-prompt as administrator. If you don’t know how to do this, you can take a look at this page.

Go to the folder c:\program files\IBM\bin and type db2oreg1 –i (if you later want to uninstall it type db2org1 –u).
commandprompt

Go to the folder c:\program files\IBM\bin and type db2oreg1 –setup (Thanks to Noel for this comment).
pic13480

This will register the DB2 ODBC-driver in Data Sources (ODBC) on Windows. You can take a look at the drivers tab.
ODBC

 

 

 

 

 

 

 

 

 

NB! If you have installed af 32-bit version of the ODBC-driver on a 64-bit system, then you have to look in the 32-bit version of the ODBC Administrator to find the driver. The 32-bit version of the ODBC Administrator can be found here: C:\Windows\SysWOW64\odbcad32.exe (Thanks to Grae for this comment)

Now you have to setup a ODBC-driver for DB2. You can either make a System or User DSN.
In this example i’m creating a User DSN. Go to the User DSN-tab and press the Add-button.
EmptyODBC

 

 

 

 

 

 

 

 

 

Choose the DB2 ODBC-driver and press the Finish-button.
ODBCAdd

 

 

 

 

 

 

 

 

 

Give the DSN a name that you can use in your programs. Here I have just given it the name DB2DB.
ODBCAddName

 

 

 

 

 

 

 

 

 

Then press the Add-button and go to the Advanced Settings-tab.
Advancedtab

Here you need to press the Add-button to add the settings below.
Database: Should point to the database that you want to connect to.
Hostname: Should point to the server where your DB2-server is installed.
Port: The portnumber where the DB2-server on the Hostname is responding.
Protocol: The protocol used to connect to the DB2-server. There’s different options as you can se in the picture below.
ProtocolOptions

The final settings should be the ones listed below.

AdvancedtabWithOptions

Now you press the OK-button and you have a new DB2 ODBC connection and your done. You can now use this DB2 ODBC connection in your different programs.
FinalCreation

 

 

 

 

 

 

 

 

 

NOTE
If you get the error described below in SAS. Then you most likely didn’t install the correct version 32/64-bit for your operating system. Install the correct bit-version of the Control Center from the IBM DB2 Client Server pack (further information at the start of this post).

The SAS/ACCESS Interface to DB2 cannot be loaded. The SASDBU code appendage could not be loaded

SAS SQL DB2 pass-through eg select

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.

SAS SQL DB2 pass-through eg index

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.

DB2 libname in SAS

The syntax is:

libname <name> db2 database=<database> schema=<schema> user=<user> password=<password> in=<tablespace>;

Eg.

libname testname db2 database=mydb schema=myschema user=domain.com\myuser password=”1234Hello” in=mytablespace;

The connection to the database has to be created eg through the DB2 ControlCenter before it is possible to connect to it in SAS.
‘in=’ determins the tablespace where the data is placed.

SAS ODBC-connection directly in SAS

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.

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.

It’s also possible to make SQL pass-through without having an ODBC-connection in the operating system (OS).

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.

It’s also possible to make a pass-through connection using an DSN created eg in the ‘Data Sources (ODBC)’ in Windows.

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