Getting path for the SAS-program being executed

/* Gets path for the SAS-program being executed */

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

%let SASProgramPath = %GetSASProgramPath;

This will only work on Windows.

In batchmode you can get the programname with the code below. This will  work in eg an AIX environment.

%let ProgramName = %sysfunc(getoption(sysin));
%put &ProgramName;

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.

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>&nbsp;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

Use SAS with multiple cores in a CPU – and other hardware considerations

SAS does use multiple cores in some of it’s procedures eg. PROC SORT, PROC SQL (order by and group by) and others. The use of parallel execution was introduced with SAS version 9. A description of how SAS uses threads can be found here.

It is possible to test the use and impact of executing SAS using multiple cores in parallel by using the OPTION NOTHREADS. This option tells SAS, not to use parallel execution when possible. OPTION THREADS is on by default. It is also possible to do some experimenting with OPTIONS CPUCOUNT where it is possible to set the number of cores for SAS to use in it’s execution of the SAS-program.
Experience says that SAS works best in a 4 core environment. Which probably has something to do with  Amdahls law. That is of course not the case if your system handles multiple users or you start multiple SAS-processes eg. through the use of RSUBMIT with multiple tasks in one SAS-session or a script that starts multiple SAS-sessions.
If you are operating in an Intel environment the type of processor you use can have a big impact on the performance of your SAS-program. Intel XEON X5000-series performs a lot better than the Intel XEON X7000-series. Rule of thumb is that for big jobs a high clockfrequency is preferable.

Another rule of thumb is to use 4GB of RAM per core on a 64bit system and 2GB of RAM per core on a 32bit system of course depending on your individual use of the available memory. Paging of the memory should of course be avoided.

Reading from SSD disks connected to the server is of course very fast but still expensive considering that you probably need some sort of RAID configuration. Writing temporary files and log files to SSD disks could give a performance advantage, but with long seriel writes cheaper disk maybe connected through a SAN might give a better performance than SSD disks.

Securing the Tabular BI Semantic Model

Summary: This paper introduces the security model for tabular BI semantic models in SQL Server 2012. You will learn how to create roles, implement dynamic security, configure impersonation settings, manage roles, and choose a method for connecting to models that works in your network security context.

Download here

Opening PowerPivot data with Excel in Sharepoint 2010

It’s is not possible to use Excel through Sharepoint 2010 on PowerPivot data, if you are using a claims based authentication provider.

Although classic mode sign-in is not required for the more common data access scenario (where PowerPivot data is extracted from the same Excel workbook that renders it) do not attempt to use PowerPivot for SharePoint with SharePoint web applications that are configured to use other authentication providers. Doing so will result in a connection failure whenever users try to connect to PowerPivot workbooks as an external data source.
Source: http://msdn.microsoft.com/en-us/library/ee210621.aspx

If anyone has a solution to this please let me know by commenting this post.
One possible solution could be to use a third party OLAP-browser for example the freeware version of Ranet Uilibrary Olap or the commercial version of Ranet Uilibrary Olap.