Monday, January 28, 2019

SAS Message Log with ODBC: COMMIT performed on connection #

When using SAS to develop high-performance queries against remote SQL databases, it is helpful to see the exact ODBC messages that SAS passes to the driver. Sometimes the implicit SQL poorly translates a query, which can be optimized. To see these message, enable the SAS trace like this:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

However, when closing the SAS process, there can be a pop-up dialog window with the title "SAS Message Log" with entries like this:

ODBC: COMMIT performed on connection #6.
ODBC: COMMIT performed on connection #5.
ODBC: COMMIT performed on connection #4.
ODBC: COMMIT performed on connection #3.
ODBC: COMMIT performed on connection #2.
ODBC: COMMIT performed on connection #1.
ODBC: COMMIT performed on connection #0.

When running SAS interactively, this is a minor nuisance. When running SAS in an automated batch, this can be a serious problem because the dialog will wait indefinitely for human interaction, so the sas.exe process will never terminate.

Sunday, January 28, 2018

Type I error rates in two-sample t-test by simulation

What do you do when analyzing data is fun, but you don't have any new data? You make it up.

This simulation tests the type I error rates of two-sample t-test in R and SAS. It demonstrates efficient methods for simulation, and it reminders the reader not to take the result of any single hypothesis test as gospel truth. That is, there is always a risk of a false positive (or false negative), so determining truth requires more than one research study.

A type I error is a false positive. That is, it happens when a hypothesis test rejects the null hypothesis when in fact it is not true. In this simulation the null hypothesis is true by design, though in the real world we cannot be sure the null hypothesis is true. This is why we write that we "fail to reject the null hypothesis" rather than "we accept it." If there were no errors in the hypothesis tests in this simulation, we would never reject the null hypothesis, but by design it is normal to reject it according to alpha, the significance level. The de facto standard for alpha is 0.05.


First, we run a simulation in R by repeatedly comparing randomly-generated sets of normally-distributed values using the two-sample t-test. Notice the simulation is vectorized: there are no "for" loops that clutter the code and slow the simulation.

Wednesday, January 10, 2018

Condition execution on row count

Use this code as a template for scenarios when you want to change how a SAS program runs depending on whether a data set is empty or not empty. For example, when a report is empty, you may want to not send an email with what would be a blank report. In other words, the report sends only when it has information.

On the other hand, you may want to send an email when a data set is empty if that means an automated SAS program had an error that requires manual intervention.

In general, it's good practice in automated SAS programs to check the size of a data sets in case they are empty or otherwise have the wrong number of observations. With one easy tweak, you could check for a specific minimum number of observations that is greater than zero. (This is left as an exercise for the reader.)

Tuesday, August 30, 2016

SAS ERROR: Cannot load SSL support. on Microsoft Windows

When using SAS with HTTPS or FTPS, which requires SSL/TLS support, you may see this error message in the SAS log.

ERROR: Cannot load SSL support.

Here is an example of code that can trigger the error.

filename myref url "";
data _null_; 
infile myref; 

The cause was that

Thursday, June 23, 2016

SAS error "insufficient memory" on remote queries with wide rows

SAS can give the error The SAS System stopped processing this step because of insufficient memory when querying a single, wide row from a remote SQL Server. The following code fully demonstrates the problem and shows a workaround. Also, I eliminate the explanation that SAS data sets in general do not support rows this wide.

Wednesday, June 8, 2016

Reusing calculated columns in Netezza and SAS queries

Netezza and SAS allow a query to reference a calculated column by name in the SELECT, WHERE, and ORDER BY clauses. Based on the DRY principle, this reduces code and makes code easier to read and maintain.

Some people call calculated columns derived or computed columns.

In Microsoft SQL Server, SQLite, and other RDBMSs you cannot exactly do this: a workaround is to reference a subquery or view. In Microsoft SQL Server, you can also define a computed column on a table.

Below is an example tested with Netezza 7.2. Notice height_m is used in the SELECT clause, and bmi is used in the WHERE and ORDER BY clauses.

Tuesday, March 15, 2016

In case of error in SAS program, send email and stop

Any automated program should check for errors and unexpected conditions, such as inability to access a resource and presence of invalid values. Unlike traditional programming languages such as Python and C# that stop processing when an error occurs, SAS barrels ahead through the rest of the program. Therefore, carelessly-written SAS programs can create unwanted side effects, such as overwriting an output data set with bad data.

Previously I wrote about a robust solution for checking SAS error codes which wraps the entire program in a macro and invokes %GOTO EXIT in case an error. This is still the ideal solution when some part of the program must continue, but it comes at a cost: wrapping SAS code in a macro disables syntax highlighting in the SAS Enhanced Editor (though not in SAS Studio). Also, it can be awkward to work with the large code block delimited by the macro, so this post focuses on two alternatives.