Tips for Using SAS® to Manipulate Large-scale Data in Databases

SAS programmers sometimes run into difficulty when transitioning from working with small databases to working with large data sets. There are two common issues that arise. One is that the data set is too large to move around during processing. In this case, the SAS log might show error messages such as “out-of-memory” or “fetch error.” A second issue arises when the SAS program will run, but it takes a very long time to finish, such as days, weeks, or longer. This situation is problematic because it exposes the project to risks from network connection failures, database service interruptions or power outages, and if in the end, results are in error, the user must start an already long process over. The first part of this paper provides a SAS Macro solution to break down a large problem into smaller pieces. This can solve issues created by code that attempts to handle too much data. The second part of the paper provides helpful tips to monitor, manage, and time lengthy SAS programs. It includes demonstrations of how to utilize the SAS log to monitor an on-going program, how to resume an interrupted SAS program, how to manage progress or results of a lengthy SAS program run on single or multiple machines in support of database systems, and how to identify system bottlenecks and evaluate performance of SAS programs and overall computing infrastructure. The paper provides examples using SAS with a PostgreSQL database system.