Measure performance in SAS

The options fullstimer; gives you a way to do comprehensive performance analysis in SAS. Below is the description of FULLSTIMER from the SAS website.

The SAS System provides the FULLSTIMER option to collect performance statistics on each SAS step, and for the job as a whole and place them in the SAS log. It is important to note that the FULLSTIMER measures only give you a snapshot view of performance at the step and job level. Each SAS port yields different FULLSTIMER statistics based on the host operating system.

Eg.

NOTE: DATA statement used:
real time                   0.06 seconds
user cpu time               0.02 seconds
system cpu time             0.00 seconds
Memory                      88k
Page Faults                  10
Page Reclaims                 0
Page Swaps                    0
Voluntary Context Switches   22
Involuntary Context Switches  0
Block Input Operations       10
Block Output Operations      12

 

Real Time The Real Time represents the elapsed time or “wall clock” time. This is the time spent to execute a job or step. This is the time the user experiences in wait for the job/step to complete. Note: As host system resources are heavily utilized the Real Time can go up significantly – representing a wait for various system resources to become available for the SAS job/step’s usage.
User CPU Time The time spent by the processor to execute user-written code. This is user-written from the perspective of the operating system and not the customer’s language statements. That is all SAS system code that is not operating system code.
System CPU Time The time spent by the processor to execute operating system tasks that support user-written code (all CPU tasks that were not executing user-written code). The user CPU time and system CPU time are mutually exclusive.
Memory Memory represents the amount of memory allocated to that job/step. This does not represent the entire amount of memory that the SAS session is consuming, as it does not reflect any SAS overhead activities (SAS manager, etc.).
Page Faults Represents the number of virtual memory page faults that occurred during the job/step. Page Faults are pages that required an I/O to retrieve (a read was done to the I/O subsystem).
Page Reclaims Represents the number of pages retrieved from the page list awaiting re-allocation (all done in memory). These pages did not require I/O activity to obtain.
Page Swaps  The number of times a process was swapped out of main memory.
Voluntary Context Switches Represents the number of times a process releases its CPU time-slice voluntarily before it’s time-slice allocation is expired. This usually occurs when the process needs an external resource, like making an I/O call for more data.
Involuntary Context Switches The number of times a process releases its CPU time-slice involuntarily. This usually happens when its CPU time-slice has expired before the task was finished, or a higher priority task takes its time-slice away.
Block Input Operations  The number of “bufsize” reads that occur. These are I/O operations to read the data into memory for usage. Not all reads have to utilize an I/O operation since the page being requested may still be cached in memory from previous reads.
Block Output Operations  This represents the number of “bufsize” writes that occur. These are the same as block input operations except that they pertain to the writes to files. As in the case of block input operations, not all block outputs will cause an I/O operation. Some files may still be cached in memory.

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”.

Setting up Microsoft BI environment

Great tutorial for setting up a Microsoft BI environment with Microsoft Sharepoint 2013 and Microsoft SQL-server 2012 Enterprise Edition. A lot of the BI features in the Microsoft platform requires an Enterprise Edition of Microsoft SQL-Server 2012.

I have also made a PDF of the tutorial.

Encryption and decryption i C#

This link has by far the most well documented guide to encryption and decryption in C#. It uses the Rijndael Class.

There are three different enryption class for AES in the .NET framework. AES is a standard of Rijndael.

When I get some more time I will write a bit more about encryption in the .NET Framework. I can recommend Pluralsights course on encryption in .NET.

Find partitioned tables in Microsoft SQL-server

This will find partitioned tables

select distinct object_name(object_id) from sys.partitions
group by object_id, index_id
having COUNT(*) > 1

This will find the partitions for a specific table

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID(‘<Schema>.<Table>’)

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

Update your security policies on Windows

If you have ever made a share on a machine running Windows and added security permissions to the share eg giving individual users rights to access the share. You might have noticed that it can take a while before these new security rights is available.

With this command (typed into the command prompt in the operating system) you can force the security rights to be updated immediately:

gpupdate /force

 

Loop a delimited macrovariabel in SAS

This shows how to loop a delimited macrovariabel.

%let Delimitor = ¤;
%let Logins = Login1¤Login2;

%let NumberOfLogins = %sysfunc(countw(&Logins., &Delimitor.));
%put Number of logins: &NumberOfLogins.;

%macro _CreateLogin;

%do J=1 %to &NumberOfLogins;

%let Login = %scan(&Logins., &J, &Delimitor.);
%CreateLogin(_Login=&Login.);

 %end;

%mend;

%_CreateLogin;

Adding path to SASAUTOS

From SAS9.2 it’s possible to add a path to eg SASAUTOS. It’s done using ‘insert’ or ‘append’.
Read more about append and insert.

options insert=(sasautos=&SASProgramPath.\Macros);

If you get a strange error, that insert isn’t recognized or something like that. The error might be caused because there’s spaces in the path, that your trying to insert.